﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HMS.BL;
using HMS.Common;
using HMS.Logging;
using HMS.Security;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Data;
using System.Collections.Specialized;
using System.Xml;
using System.Xml.Serialization;
using System.IO;
//using Microsoft.SqlServer.Management.Smo;
using System.Threading;

namespace HMS.DL
{

    public class rel
    {
        public string show_col { get; set; }
        public string rel_col { get; set; }
    }

    public class LstIds
    {
        public decimal Id { get; set; }
    }

    public class dict
    {
        public string key1 { get; set; }
        public string value { get; set; }
    }

    public class LstManualIds
    {
        public decimal Id { get; set; }
        public decimal mastId { get; set; }
    }


    public class SrcColumns
    {
        public string colname { get; set; }
        public string colvalue { get; set; }
        public Boolean changed { get; set; }

        public SrcColumns()
        {
        }
    }

    public class MasterData
    {

        private const string ClsName = "$MasterData$";

        public string dimTableName { get; set; }
        public Boolean bShowDetailLink { get; set; }

        public string ChilddimTableName { get; set; }

        public Int64 ListId { get; set; }
        public int ChildListId { get; set; }
        public int Imp_Id { get; set; }
        public int id { get; set; }
        public Int64 MasterId { get; set; }

        //  public coll reqformVal{ get; set; }
        public NameValueCollection allkeys { get; set; }

        public string srcVal { get; set; }
        public string sidx { get; set; }
        public string sord { get; set; }
        public int rows { get; set; }
        public int page { get; set; }
        public int limit { get; set; }
        public string query { get; set; }
        public string queryObj { get; set; }
        public bool _search { get; set; }
        public string strfilePath { get; set; }
        public string strfileName { get; set; }
        public string strSheetName { get; set; }

        public decimal MASTER_ID { get; set; }
        public decimal MANUAL_RULE_ID { get; set; }
        public int RuleId { get; set; }
        public string Ids { get; set; }
        public List<LstIds> LstIds { get; set; }
        // public List<dict> allkey { get; set; }
        public List<LstManualIds> LstManualIds { get; set; }
        public string[] arrMastId { get; set; }

        //graph
        public Int64 ParentId { get; set; }
        public string ParentIds { get; set; }

        public string GetMaster()
        {
            try
            {

                Connection Conn = new Connection();

                //original before SP
                //string strQry = " SELECT LIST_ID,TABLE_NAME,LIST_NAME FROM X_MASTER_LIST ";
                ////string strQry = " SELECT * FROM t";//for oracle
                //Util.WritInfoLog(ClsName, "GetMaster", "Query for GetMaster", string.Format("Query:{0}", strQry));
                //DataSet ds1 = Conn.GetDataSetFromFactory(strQry);
                //Util.WritInfoLog(ClsName, "GetMaster", "End Query for GetMaster");
                //            
                Util.WritInfoLog(ClsName, "GetMaster", "method for GetMaster");
                DataSet ds = Conn.GetDataSetFromFactoryProc("usp_GetMasterData");
                Util.WritInfoLog(ClsName, "GetMaster", "End method for GetMaster");

                List<DropdownListItem> listMaster = new List<DropdownListItem>();
                listMaster.Add(new DropdownListItem("--Select--", ""));

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    listMaster.Add(new DropdownListItem(dr["LIST_NAME"].ToString(), dr["LIST_ID"].ToString(), dr["TABLE_NAME"].ToString()));
                }

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(listMaster);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetMaster", "Error in GetMaster", "", ex);
                throw ex;
            }
        }

        public string GetFields()
        {
            try
            {

                Connection sqlConn = new Connection();

                //original before SP
                //string strQry = " SELECT DISPLAY,FIELD_NAME,DATA_TYPE FROM X_MASTER_LIST_COLUMNS";
                //strQry = strQry + " WHERE LIST_ID =" + ListId;

                //Util.WritInfoLog(ClsName, "GetFields", "Query for GetFields", string.Format("Query:{0}", strQry));
                //DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
                //Util.WritInfoLog(ClsName, "GetFields", "End Query for GetFields");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));

                Util.WritInfoLog(ClsName, "GetFields", "method for usp_GetDimensionDataByListId");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetDimensionDataByListId", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetFields", "End method for usp_GetDimensionDataByListId");


                List<DropdownListItem> listMaster = new List<DropdownListItem>();
                listMaster.Add(new DropdownListItem("--Select--", ""));
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    // listMaster.Add(new DropdownListItem(dr["HIER_NAME"].ToString(), dr["HIERARCHY_ID"].ToString(), dr["NODE_TABLE_NAME"].ToString()));
                    listMaster.Add(new DropdownListItem(dr["DISPLAY"].ToString(), dr["FIELD_NAME"].ToString(), dr["DATA_TYPE"].ToString()));
                }

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(listMaster);
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetFields", "Error in GetFields", "", ex);
                throw ex;
            }
        }

        public string GetGridColdata()
        {
            try
            {

                string primarykey = string.Empty;

                Connection sqlConn = new Connection();

                //string strQry = " SELECT * FROM " + dimTableName;

                //original before SP
                //string strQry = " SELECT * FROM X_MASTER_LIST_COLUMNS ";
                //strQry = strQry + " WHERE LIST_ID= " + ListId;

                //Util.WritInfoLog(ClsName, "GetGridColdata", "Query for GetGridColdata", string.Format("Query:{0}", strQry));
                //DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
                //Util.WritInfoLog(ClsName, "GetGridColdata", "End Query for GetGridColdata");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));

                Util.WritInfoLog(ClsName, "GetGridColdata", "method for usp_GetDimensionDataByListId");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetDimensionDataByListId", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetGridColdata", "End method for usp_GetDimensionDataByListId");

                DataTable dt = ds.Tables[0];

                if (bShowDetailLink == true)
                {
                    DataRow dr = dt.NewRow();
                    dr[0] = 0; dr[1] = 0; dr[2] = "Detail";
                    dr[3] = "xselect"; dr[4] = "varchar"; dr[5] = 8;
                    dr[6] = 1; dr[7] = 0; dr[8] = "textbox";
                    dr[9] = 0; dr[10] = 0; dt.Rows.InsertAt(dr, 0);

                    DataRow dr1 = dt.NewRow();
                    dr1[0] = 0; dr1[1] = 0; dr1[2] = "row";
                    dr1[3] = "row"; dr1[4] = "varchar"; dr1[5] = 8;
                    dr1[6] = 1; dr1[7] = 0; dr1[8] = "textbox";
                    dr1[9] = 1; dr1[10] = 0; dt.Rows.InsertAt(dr1, 1);

                }

                List<colModel> listColmodel = new List<colModel>();
                List<string> columns = new List<string>();

                int index = 0;
                //
                foreach (DataRow dr in ds.Tables[0].Rows)
                {

                    //for columns
                    columns.Add(dr["DISPLAY"].ToString());

                    //for colmodel
                    colModel colmodel = new colModel();

                    colmodel.index = dr["FIELD_NAME"].ToString();
                    colmodel.name = dr["FIELD_NAME"].ToString();
                    colmodel.width = 150;

                    Dictionary<string, object> edoptions = new Dictionary<string, object>();
                    Dictionary<string, object> editrules = new Dictionary<string, object>();
                    Dictionary<string, object> formatoptions = new Dictionary<string, object>();

                    if (Convert.ToString(dr["PRECISION"]) != "")
                    {
                        //edoptions.Add("size", Convert.ToInt32(dr["PRECISION"]));
                        //edoptions.Add("width", Convert.ToInt32(dr["PRECISION"]));
                        edoptions.Add("maxlength", Convert.ToInt32(dr["PRECISION"]));

                    }

                    if (Convert.ToString(dr["IS_REQUIRED"]).ToLower() == "true")
                    {
                        editrules.Add("required", true);
                    }

                    if (dr["IS_PRIMARY"].ToString().ToLower() == "true")
                    {
                        primarykey = dr["FIELD_NAME"].ToString();
                        //colmodel.editable = false;//sorttype:'int'
                        //colmodel.sorttype = "int";//depending on datatype do it from X_MASTER_LIST_COLUMNS
                        // Dictionary<string, Boolean> edoption = new Dictionary<string, Boolean>();
                        //editrules:{required:true, edithidden:true}, 
                        //colmodel.hiddden = true;
                        //colmodel.editoptions = edoptions;
                        edoptions.Add("required", true);
                        edoptions.Add("edithidden", true);
                        colmodel.key = true;
                        colmodel.editable = false;
                        //editrules.Add("edithidden", true); 
                    }
                    else
                    {
                        colmodel.editable = true;
                    }

                    if (dr["DATA_TYPE"].ToString().ToLower() == "number") //integer for whole number
                    {
                        colmodel.sorttype = "int";
                        colmodel.align = "right";
                        editrules.Add("number", true);
                        edoptions.Add("NullIfEmpty", true);
                        //colmodel.NullIfEmpty = "true";                        
                        //formatter 
                        colmodel.formatter = "number";
                        //format option for number
                        formatoptions.Add("decimalSeparator", ".");
                        formatoptions.Add("defaulValue", 0);
                        formatoptions.Add("decimalPlaces", 2);
                    }
                    else if (dr["DATA_TYPE"].ToString().ToLower() == "varchar")
                    {
                        //colmodel.sorttype = "text";
                        colmodel.sorttype = "string";
                        colmodel.align = "left";
                        //colmodel.stype = "string";
                    }
                    else if (dr["DATA_TYPE"].ToString().ToLower() == "date")
                    {
                        colmodel.sorttype = "date";
                        colmodel.align = "right";
                        //edoptions.Add("dataInit","function(el) {  $(el).datepicker({ dateFormat: 'mm-dd-yy' });");
                        //edoptions.Add("dataInit"," $(el).datepicker();");
                        editrules.Add("date", true);
                        colmodel.formatter = "date";
                        ////date format in mm/dd/yyyy
                        formatoptions.Add("srcformat", "ISO8601Long");
                        formatoptions.Add("newformat", "m/d/Y");
                        //edoptions.Add("type", "date");
                        //  edoptions.Add("editoptions", "js:function(elm){}");                         
                        // "editoptions"=>array("editoptions"=> 
                        //"js:function(elm){setTimeout(function(){ 
                        //    jQuery(elm).datepicker({dateFormat:'yy-mm-dd'}); 
                        //    jQuery('.ui-datepicker').css({'font-size':'75%'}); 
                        //},200);}") 
                    }
                    else if (dr["DATA_TYPE"].ToString().ToLower() == "float")
                    {
                        colmodel.sorttype = "float";
                        colmodel.align = "right";
                    }

                    //control type
                    if (dr["CONTROL_TYPE"].ToString().ToLower() == "textarea")
                    {
                        colmodel.edittype = dr["CONTROL_TYPE"].ToString().ToLower();
                        edoptions.Add("rows", "2");
                        edoptions.Add("cols", "20");
                    }
                    else if (dr["CONTROL_TYPE"].ToString().ToLower() == "checkbox")
                    {
                        colmodel.edittype = "checkbox";//,editoptions:{value:"Yes:No"} 
                        edoptions.Add("value", "Yes:No");
                        colmodel.formatter = "checkbox";
                    }
                    else if (dr["CONTROL_TYPE"].ToString().ToLower() == "dropdown")
                    {
                        colmodel.edittype = "select";//editoptions:{value:"FE:FedEx;TN:TNT"}
                        //colmodel.formatter = "select";
                        string drpval = formDropdownString(dr["DROPDOWN_VAL"].ToString());
                        edoptions.Add("value", drpval); //values in this format
                        edoptions.Add("style", "width:160px");
                        // edoptions.Add("value", "0:Select;FE:FedEx;TN:TNT"); //values in this format
                        //edoptions.Add("size", 400);
                    }

                    if (dr["DISPLAY"].ToString() == "Id" || dr["FIELD_NAME"].ToString() == "row")
                    {
                        colmodel.hidden = true;
                    }

                    if (dr["FIELD_NAME"].ToString() == "xselect")
                    {
                        colmodel.formatter = "linkShowFmatter"; //buttonize
                        colmodel.align = "center";
                        colmodel.width = 60;
                    }

                    colmodel.editoptions = edoptions;
                    colmodel.editrules = editrules;
                    colmodel.formatoptions = formatoptions;

                    listColmodel.Add(colmodel);
                    index++;
                }

                Grid griddata = new Grid();
                griddata.colModelList = listColmodel;
                griddata.columns = columns;
                griddata.primarykey = primarykey;

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(griddata);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetGridColdata", "Error in GetGridColdata", "", ex);
                throw ex;
            }
        }

        //with stored procedure
        public string GetGriddata()
        {
            try
            {

                string primarykey = string.Empty;
                string strData = string.Empty;

                Connection sqlConn = new Connection();
                if (sidx == "" || sidx == null)
                {
                    sidx = " DIM_ID ";
                }

                string sortExp = sidx + " " + sord;
                int start = ((page - 1) * rows);

                List<DBParameter> lstparam = new List<DBParameter>();

                lstparam.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));
                lstparam.Add(new DBParameter("@start", DbType.Int32, ParameterDirection.Input, 0, start));
                lstparam.Add(new DBParameter("@rows", DbType.Int32, ParameterDirection.Input, 0, rows));
                lstparam.Add(new DBParameter("@query", DbType.String, ParameterDirection.Input, 1000, query.Replace("\"", "'")));
                lstparam.Add(new DBParameter("@sortExp", DbType.String, ParameterDirection.Input, 1000, sortExp));
                lstparam.Add(new DBParameter("@_search", DbType.Boolean, ParameterDirection.Input, 0, _search));
                lstparam.Add(new DBParameter("@dimTableName", DbType.String, ParameterDirection.Input, 1000, dimTableName));

                Util.WritInfoLog(ClsName, "GetGriddata", "method for GetGriddata");
                DataSet dsdata = sqlConn.GetDataSetFromFactoryProc("usp_GetDimensionGridData", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetGriddata", "End method for GetGriddata");

                //do for paging 
                if (dsdata.Tables.Count != 0)
                {
                    DataTable dtData = dsdata.Tables[0];
                    int cnt = 0;
                    cnt = Convert.ToInt32(dsdata.Tables[1].Rows[0]["CNT"]);
                    strData = Common.JsonForJqgrid(dtData, rows, cnt, page);
                }

                JavaScriptSerializer js = new JavaScriptSerializer();
                return strData;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetGriddata", "Error in GetGriddata", "", ex);
                throw ex;
            }
        }

        //original before stored procedure
        //public string GetGriddata()
        //{
        //    try
        //    {

        //        string primarykey = string.Empty;
        //        string strData = string.Empty;

        //        Connection sqlConn = new Connection();

        //        string strQry = " SELECT FIELD_NAME,IS_PRIMARY FROM X_MASTER_LIST_COLUMNS ";
        //        strQry = strQry + " WHERE LIST_ID= " + ListId;
        //        //strQry = strQry + " AND IS_PRIMARY ='TRUE' ";  

        //        Util.WritInfoLog(ClsName, "GetGriddata", "Query for get columns in GetGriddata", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "GetGriddata", "End Query for get columns in GetGriddata");

        //        DataTable dt = ds.Tables[0];

        //        if (dt.Rows.Count > 0)
        //        {

        //            DataTable dtcol = ds.Tables[0];
        //            DataView dv = new DataView(dtcol);

        //            dv.RowFilter = " IS_PRIMARY =TRUE";

        //            var names = from DataRow row in dt.Rows
        //                        select (string)row["FIELD_NAME"];
        //            string[] arr = names.ToArray();
        //            string cols = string.Join(", ", arr);

        //            if (sidx == "" || sidx == null)
        //            {
        //                //sidx = dv[0]["FIELD_NAME"].ToString();
        //                sidx = " DIM_ID ";
        //            }

        //            string sortExp = sidx + " " + sord;
        //            int start = ((page - 1) * rows);

        //            string strQryy = " SELECT " + cols + " FROM ";
        //            strQryy = strQryy + " (SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + ") AS Row,";
        //            strQryy = strQryy + " * FROM  " + dimTableName;
        //            //for search filter
        //            if (_search && (query != null || query != ""))
        //            {
        //                strQryy = strQryy + " WHERE " + query.Replace("\"", "'");//errString.Replace("docment", "document");
        //            }
        //            strQryy = strQryy + ") tmp ";
        //            strQryy = strQryy + " WHERE Row > " + start + " AND Row <= " + (start + rows);

        //            strQryy = strQryy + " SELECT COUNT(*) CNT FROM  " + dimTableName;

        //            Util.WritInfoLog(ClsName, "GetGriddata", "Query for GetGriddata", string.Format("Query:{0}", strQryy));
        //            DataSet dsdata = sqlConn.GetDataSetFromFactory(strQryy);
        //            Util.WritInfoLog(ClsName, "GetGriddata", "End Query for GetGriddata");

        //            //do for paging 
        //            DataTable dtData = dsdata.Tables[0];
        //            int cnt = 0;
        //            cnt = Convert.ToInt32(dsdata.Tables[1].Rows[0]["CNT"]);                   
        //            strData = Common.JsonForJqgrid(dtData, rows, cnt, page);
        //        }

        //        JavaScriptSerializer js = new JavaScriptSerializer();
        //        return strData;

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "GetGriddata", "Error in GetGriddata", "", ex);
        //        throw ex;
        //    }
        //}

        //public string GetGriddata()
        //{
        //    try
        //    {

        //        string primarykey = string.Empty;

        //        Connection sqlConn = new Connection();
        //        string strQry = " SELECT * FROM " + dimTableName;

        //        strQry = strQry + " SELECT * FROM X_MASTER_LIST_COLUMNS ";
        //        strQry = strQry + " WHERE LIST_ID= " + ListId;

        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        DataTable dt = ds.Tables[0];

        //        List<colModel> listColmodel = new List<colModel>();
        //        List<string> columns = new List<string>();

        //        int index = 0;
        //        //
        //        foreach (DataRow dr in ds.Tables[1].Rows)
        //        {

        //            //for columns
        //            columns.Add(dr["DISPLAY"].ToString());

        //            //for colmodel
        //            colModel colmodel = new colModel();

        //            colmodel.index = dr["FIELD_NAME"].ToString();
        //            colmodel.name = dr["FIELD_NAME"].ToString();
        //            colmodel.width = 150;

        //            Dictionary<string, object> edoptions = new Dictionary<string, object>();
        //            Dictionary<string, object> editrules = new Dictionary<string, object>();
        //            Dictionary<string, object> formatoptions = new Dictionary<string, object>();

        //            if (Convert.ToString(dr["PRECISION"]) != "")
        //            {
        //                //edoptions.Add("size", Convert.ToInt32(dr["PRECISION"]));
        //                //edoptions.Add("width", Convert.ToInt32(dr["PRECISION"]));
        //                edoptions.Add("maxlength", Convert.ToInt32(dr["PRECISION"]));

        //            }

        //            if (Convert.ToString(dr["IS_REQUIRED"]).ToLower() == "true")
        //            {
        //                editrules.Add("required", true);
        //            }

        //            if (dr["IS_PRIMARY"].ToString().ToLower() == "true")
        //            {
        //                primarykey = dr["FIELD_NAME"].ToString();
        //                //colmodel.editable = false;//sorttype:'int'
        //                //colmodel.sorttype = "int";//depending on datatype do it from X_MASTER_LIST_COLUMNS
        //                // Dictionary<string, Boolean> edoption = new Dictionary<string, Boolean>();
        //                //editrules:{required:true, edithidden:true}, 
        //                //colmodel.hiddden = true;
        //                //colmodel.editoptions = edoptions;

        //                edoptions.Add("required", true);
        //                edoptions.Add("edithidden", true);
        //                colmodel.key = true;
        //                colmodel.editable = false;

        //                //editrules.Add("edithidden", true); 

        //            }
        //            else
        //            {
        //                colmodel.editable = true;
        //            }

        //            if (dr["DATA_TYPE"].ToString().ToLower() == "number") //integer for whole number
        //            {
        //                colmodel.sorttype = "int";
        //                colmodel.align = "right";

        //                editrules.Add("number", true);
        //                edoptions.Add("NullIfEmpty", true);
        //                //colmodel.NullIfEmpty = "true";                        
        //                //formatter 
        //                colmodel.formatter = "number";
        //                //format option for number
        //                formatoptions.Add("decimalSeparator", ".");
        //                formatoptions.Add("defaulValue", 0);
        //                formatoptions.Add("decimalPlaces", 2);
        //            }
        //            else if (dr["DATA_TYPE"].ToString().ToLower() == "varchar")
        //            {
        //                //colmodel.sorttype = "text";
        //                colmodel.sorttype = "string";
        //                colmodel.align = "left";
        //                //colmodel.stype = "string";

        //            }
        //            else if (dr["DATA_TYPE"].ToString().ToLower() == "date")
        //            {

        //                colmodel.sorttype = "date";
        //                colmodel.align = "right";
        //                //edoptions.Add("dataInit","function(el) {  $(el).datepicker({ dateFormat: 'mm-dd-yy' });");
        //                //edoptions.Add("dataInit"," $(el).datepicker();");
        //                editrules.Add("date", true);
        //                colmodel.formatter = "date";
        //                ////date format in mm/dd/yyyy
        //                formatoptions.Add("srcformat", "ISO8601Long");
        //                formatoptions.Add("newformat", "m/d/Y");
        //                //edoptions.Add("type", "date");
        //                //  edoptions.Add("editoptions", "js:function(elm){}");                         
        //                // "editoptions"=>array("editoptions"=> 
        //                //"js:function(elm){setTimeout(function(){ 
        //                //    jQuery(elm).datepicker({dateFormat:'yy-mm-dd'}); 
        //                //    jQuery('.ui-datepicker').css({'font-size':'75%'}); 
        //                //},200);}") 
        //            }
        //            else if (dr["DATA_TYPE"].ToString().ToLower() == "float")
        //            {
        //                colmodel.sorttype = "float";
        //                colmodel.align = "right";
        //            }

        //            //control type
        //            if (dr["CONTROL_TYPE"].ToString().ToLower() == "textarea")
        //            {
        //                colmodel.edittype = dr["CONTROL_TYPE"].ToString().ToLower();
        //                edoptions.Add("rows", "2");
        //                edoptions.Add("cols", "20");
        //            }
        //            else if (dr["CONTROL_TYPE"].ToString().ToLower() == "checkbox")
        //            {
        //                colmodel.edittype = "checkbox";//,editoptions:{value:"Yes:No"} 
        //                edoptions.Add("value", "Yes:No");
        //                colmodel.formatter = "checkbox";
        //            }
        //            else if (dr["CONTROL_TYPE"].ToString().ToLower() == "dropdown")
        //            {
        //                colmodel.edittype = "select";//editoptions:{value:"FE:FedEx;TN:TNT"}
        //                //colmodel.formatter = "select";
        //                string drpval = formDropdownString(dr["DROPDOWN_VAL"].ToString());
        //                edoptions.Add("value", drpval); //values in this format
        //                edoptions.Add("style", "width:160px");

        //                // edoptions.Add("value", "0:Select;FE:FedEx;TN:TNT"); //values in this format
        //                //edoptions.Add("size", 400);
        //            }

        //            colmodel.editoptions = edoptions;
        //            colmodel.editrules = editrules;
        //            colmodel.formatoptions = formatoptions;

        //            listColmodel.Add(colmodel);
        //            index++;

        //        }


        //        List<object> rows = new List<object>();

        //        foreach (DataRow dr in dt.Rows)
        //        {

        //            Dictionary<string, object> row = new Dictionary<string, object>();
        //            foreach (DataColumn dc in dt.Columns)
        //            {
        //                row.Add(dc.ColumnName, dr[dc].ToString());
        //            }
        //            rows.Add(row);
        //        }

        //        Grid griddata = new Grid();
        //        griddata.colModelList = listColmodel;
        //        griddata.columns = columns;
        //        griddata.datarow = rows;
        //        griddata.primarykey = primarykey;

        //        JavaScriptSerializer js = new JavaScriptSerializer();
        //        return js.Serialize(griddata);

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "GetGriddata", "Error in GetGriddata", "", ex);
        //        throw ex;
        //    }
        //}

        public string DelData()
        {
            try
            {

                Connection sqlConn = new Connection();

                //original before SP
                ////get primary key column
                //string strQry = " SELECT FIELD_NAME FROM X_MASTER_LIST_COLUMNS ";
                //strQry = strQry + " WHERE LIST_ID =" + ListId;
                //// strQry = strQry + " AND IS_REQUIRED =1";
                //strQry = strQry + " AND IS_PRIMARY =1";

                ////get relation table name
                //strQry = strQry + " SELECT TABLE_NAME FROM X_HIER_MASTER_LIST_REL ";
                //strQry = strQry + " WHERE MASTER_LIST_ID =" + ListId;

                //Util.WritInfoLog(ClsName, "DelData", "Query for getting primary key column and Rel table name", string.Format("Query:{0}", strQry));
                //DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
                //Util.WritInfoLog(ClsName, "DelData", "End Query for getting primary key column and Rel table name");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));

                Util.WritInfoLog(ClsName, "GetGriddata", "method for getting Rel table name");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetDimensionRelTable", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetGriddata", "End method for getting Rel table name");

                bool bdel = false;

                if (ds.Tables[0].Rows.Count > 0)
                {
                    //check validity
                    //bdel = ValidateDelete(ds.Tables[1].Rows[0]["TABLE_NAME"].ToString(), ds.Tables[0].Rows[0]["FIELD_NAME"].ToString());
                    bdel = ValidateDelete(ds.Tables[0].Rows[0]["TABLE_NAME"].ToString(), " DIM_ID ");
                }
                else
                {
                    bdel = true;
                }

                if (bdel == true)
                {

                    //original before SP
                    ////check before delete in _rel table (get from X_HIER_MASTER_LIST_REL)
                    //string strDelQry = " DELETE FROM " + dimTableName;
                    //strDelQry = strDelQry + " WHERE " + ds.Tables[0].Rows[0]["FIELD_NAME"].ToString() + "=" + id;

                    //Util.WritInfoLog(ClsName, "DelData", "Query for DelData", string.Format("Query:{0}", strDelQry));
                    //Int32 res = sqlConn.SaveDataByFactory(strDelQry);
                    //Util.WritInfoLog(ClsName, "DelData", "End Query for DelData");

                    List<DBParameter> lstparam1 = new List<DBParameter>();
                    lstparam1.Add(new DBParameter("@id", DbType.Int32, ParameterDirection.Input, 0, id));
                    lstparam1.Add(new DBParameter("@dimTableName", DbType.String, ParameterDirection.Input, 255, dimTableName));

                    Util.WritInfoLog(ClsName, "GetGriddata", "method for DelData");
                    Int32 res = sqlConn.SaveDataByFactoryByProc("usp_DeleteDimData", ref(lstparam1));
                    Util.WritInfoLog(ClsName, "GetGriddata", "End method for DelData");

                    if (res == 1) return "true";
                    else return "false";
                    //return res.ToString();
                }
                else
                {
                    return "Can't Dlete data in use.";
                }

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "DelData", "Error in DelData", "", ex);
                return "false";
                throw ex;
            }
        }

        public bool ValidateDelete(string strTableName, string strprimarykey)
        {

            try
            {

                Connection sqlConn = new Connection();

                //original before SP
                //string strQry = " SELECT COUNT(*) FROM " + strTableName;
                //strQry = strQry + " WHERE " + strprimarykey + "= " + id;

                //Util.WritInfoLog(ClsName, "ValidateDelete", "Query for ValidateDelete", string.Format("Query:{0}", strQry));
                //Int32 intCnt = Convert.ToInt32(sqlConn.ExecuteScalarByFactory(strQry));
                //Util.WritInfoLog(ClsName, "ValidateDelete", "End Query for ValidateDelete");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@id", DbType.Int32, ParameterDirection.Input, 0, id));
                lstparam.Add(new DBParameter("@dimRelTableName", DbType.String, ParameterDirection.Input, 255, strTableName));

                Util.WritInfoLog(ClsName, "GetGriddata", "method for ValidateDelete");
                Int32 intCnt = Convert.ToInt32(sqlConn.ExecuteScalarByFactory("usp_ValidateDimDelete", ref(lstparam)));
                Util.WritInfoLog(ClsName, "GetGriddata", "End method for ValidateDelete");

                if (intCnt == 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "ValidateDelete", "Error in ValidateDelete", "", ex);
                throw ex;
            }
        }

        public string EditData()
        {
            try
            {

                Connection sqlConn = new Connection();

                //new one - after stored procedure
                List<dict> allkey = new List<dict>();

                foreach (string key in allkeys)
                {
                    dict d = new dict();
                    d.key1 = key;
                    d.value = allkeys[key];
                    allkey.Add(d);
                }

                var xmlDocument = new XmlDocument();
                var serializer = new XmlSerializer(typeof(List<dict>));

                string[,] a = new string[,]
	            {
	                {"ant", "aunt"},
	                {"Sam", "Samantha"},
	                {"clozapine", "quetiapine"},
	                {"flomax", "volmax"},
	                {"toradol", "tramadol"}
	            };

                var serializer1 = new XmlSerializer(typeof(string[,]));

                using (var stream = new MemoryStream())
                {
                    serializer.Serialize(stream,a);
                    stream.Flush(); stream.Seek(0, SeekOrigin.Begin);
                    xmlDocument.Load(stream);
                }

                using (var stream = new MemoryStream())
                {
                    serializer.Serialize(stream, allkey);
                    stream.Flush(); stream.Seek(0, SeekOrigin.Begin);
                    xmlDocument.Load(stream);
                }

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@field_Xml", DbType.Xml, ParameterDirection.Input, 0, xmlDocument.OuterXml));
                lstparam.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));
                lstparam.Add(new DBParameter("@dimTableName", DbType.String, ParameterDirection.Input, 255, dimTableName));
                lstparam.Add(new DBParameter("@_id", DbType.String, ParameterDirection.Input, 0, allkeys["_id"]));
                lstparam.Add(new DBParameter("@returnValue", DbType.Int16, ParameterDirection.ReturnValue));

                Util.WritInfoLog(ClsName, "EditData", "method for EditData");
                Int32 res11 = Convert.ToInt32(sqlConn.SaveDataByFactoryByProc("usp_EditDimData", ref(lstparam)));
                Util.WritInfoLog(ClsName, "EditData", "End method for EditData");

                int retVal = 0;

                foreach (DBParameter dbparam in lstparam)
                {
                    if (dbparam.name == "@returnValue")
                    {
                        retVal = Convert.ToInt32(dbparam.value);
                        break;
                    }
                }

                // SoapFormatter ser = new SoapFormatter();
                // MemoryStream ms = new MemoryStream();
                // ser.Serialize(ms, allkeys);           
                // ms.Seek(0, System.IO.SeekOrigin.Begin);
                // StreamReader streamReader = new StreamReader(ms);
                // string output = streamReader.ReadToEnd();
                //// xmlDocument.Load(output);

                // byte[] b = ms.GetBuffer();
                // string s = Encoding.Default.GetString(b);
                // ms.Close();

                ////old one - after stored procedure
                ////get columns from table 
                //string strQry = " SELECT * FROM X_MASTER_LIST_COLUMNS ";
                //strQry = strQry + " WHERE LIST_ID =" + ListId;

                //Util.WritInfoLog(ClsName, "EditData", "Query for getting column names", string.Format("Query:{0}", strQry));
                //DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
                //Util.WritInfoLog(ClsName, "EditData", "End Query for getting column names");

                //DataTable dtcol = ds.Tables[0];
                //DataView Dv = new DataView(dtcol);
                //Dv.RowFilter = " IS_PRIMARY = FALSE ";

                //string strUpdate = "UPDATE  " + dimTableName + " SET ";

                //foreach (DataRowView dr in Dv)
                //{

                //    strUpdate = strUpdate + dr["FIELD_NAME"].ToString();
                //    if (dr["DATA_TYPE"].ToString().ToLower() == "varchar" || dr["DATA_TYPE"].ToString().ToLower() == "date" || dr["DATA_TYPE"].ToString().ToLower() == "char")
                //    {
                //        strUpdate = strUpdate + " = '" + allkeys[dr["FIELD_NAME"].ToString()] + "',";
                //    }
                //    else if (dr["DATA_TYPE"].ToString().ToLower() == "number")
                //    {

                //        // if (allkeys[dr["FIELD_NAME"].ToString()] == "null") strUpdate = strUpdate + " = " + 0 + ",";
                //        // else strUpdate = strUpdate + " = " + allkeys[dr["FIELD_NAME"].ToString()] + ",";
                //        strUpdate = strUpdate + " = " + allkeys[dr["FIELD_NAME"].ToString()] + ",";
                //    }
                //}

                //strUpdate = strUpdate.Substring(0, strUpdate.Length - 1);

                ////where condition
                //Dv.RowFilter = " IS_PRIMARY = TRUE ";
                //strUpdate = strUpdate + " WHERE " + Dv[0]["FIELD_NAME"].ToString();
                //strUpdate = strUpdate + " = " + allkeys["_id"];

                ////JavaScriptSerializer js = new JavaScriptSerializer();
                //Util.WritInfoLog(ClsName, "EditData", "Query for EditData", string.Format("Query:{0}", strUpdate));
                //Int32 res = sqlConn.SaveDataByFactory(strUpdate);
                //Util.WritInfoLog(ClsName, "EditData", "End Query for EditData");

                if (retVal == 0) return "false";
                else return "true";

                // return res.ToString();
                //return js.Serialize(new Records(sqlConn.ExecuteCommand(strUpdate)));

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "EditData", "Error in EditData", "", ex);
                return "false";
                throw ex;
            }
        }


        public string AddData()
        {
            try
            {

                Connection sqlConn = new Connection();

                //new one - after stored procedure
                List<dict> allkey = new List<dict>();

                foreach (string key in allkeys)
                {
                    dict d = new dict();
                    d.key1 = key;
                    d.value = allkeys[key];
                    allkey.Add(d);
                }

                var xmlDocument = new XmlDocument();
                var serializer = new XmlSerializer(typeof(List<dict>));

                using (var stream = new MemoryStream())
                {
                    serializer.Serialize(stream, allkey);
                    stream.Flush(); stream.Seek(0, SeekOrigin.Begin);
                    xmlDocument.Load(stream);
                }

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@field_Xml", DbType.Xml, ParameterDirection.Input, 0, xmlDocument.OuterXml));
                lstparam.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));
                lstparam.Add(new DBParameter("@dimTableName", DbType.String, ParameterDirection.Input, 255, dimTableName));
                lstparam.Add(new DBParameter("@returnValue", DbType.Int16, ParameterDirection.ReturnValue, 0));

                Util.WritInfoLog(ClsName, "AddData", "method for AddData");
                Int32 res = Convert.ToInt32(sqlConn.SaveDataByFactoryByProc("usp_AddDimData", ref(lstparam)));
                Util.WritInfoLog(ClsName, "AddData", "End method for AddData");

                int retVal = 0;
                foreach (DBParameter dbparam in lstparam)
                {
                    if (dbparam.name == "@returnValue")
                    {
                        retVal = Convert.ToInt32(dbparam.value);
                        break;
                    }
                }

                if (retVal == 0) return "false";
                else return Convert.ToString(res);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "AddData", "Error in AddData", "", ex);
                throw ex;
            }
        }


        //original before SP
        //public string AddData()
        //{
        //    try
        //    {

        //        Connection sqlConn = new Connection();

        //        //get columns from table -0
        //        string strQry = " SELECT * FROM X_MASTER_LIST_COLUMNS ";
        //        strQry = strQry + " WHERE LIST_ID =" + ListId;

        //        Util.WritInfoLog(ClsName, "AddData", "Query for getting column names", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "AddData", "End Query for getting column names");

        //        DataTable dtcol = ds.Tables[0];
        //        DataView Dv = new DataView(dtcol);
        //        Dv.RowFilter = " IS_PRIMARY = TRUE ";

        //        //get max + 1  id -1
        //        strQry = " SELECT MAX(" + Dv[0]["FIELD_NAME"].ToString() + ")+ 1 AS ID FROM " + dimTableName;

        //        Util.WritInfoLog(ClsName, "AddData", "Query for getting max + 1  id", string.Format("Query:{0}", strQry));
        //        DataSet dsMax = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "AddData", "End Query for getting max + 1  id");

        //        Dv.RowFilter = " IS_PRIMARY = FALSE ";
        //        string strInsert = "INSERT INTO  " + dimTableName;

        //        string colNm = string.Empty;
        //        string colVl = string.Empty;

        //        foreach (DataRowView dr in Dv)
        //        {

        //            colNm = colNm + dr["FIELD_NAME"].ToString() + ",";

        //            if (dr["DATA_TYPE"].ToString().ToLower() == "varchar")
        //            {
        //                colVl = colVl + "'" + allkeys[dr["FIELD_NAME"].ToString()] + "'" + ",";
        //            }
        //            else if (dr["DATA_TYPE"].ToString().ToLower() == "char")
        //            {
        //                colVl = colVl + "'" + allkeys[dr["FIELD_NAME"].ToString()] + "'" + ",";
        //            }
        //            else if (dr["DATA_TYPE"].ToString().ToLower() == "date")
        //            {
        //                colVl = colVl + "'" + Convert.ToDateTime(allkeys[dr["FIELD_NAME"].ToString()]).ToString("MM/dd/yyyy") + "',"; // Convert.ToDateTime(EndDate).ToString("MM/dd/yyyy")
        //            }
        //            else if (dr["DATA_TYPE"].ToString().ToLower() == "number")
        //            {
        //                colVl = colVl + allkeys[dr["FIELD_NAME"].ToString()] + ",";
        //            }
        //        }

        //        colNm = colNm.Substring(0, colNm.Length - 1);
        //        colVl = colVl.Substring(0, colVl.Length - 1);

        //        Dv.RowFilter = " IS_PRIMARY = TRUE ";

        //        strInsert = strInsert + "(" + Dv[0]["FIELD_NAME"].ToString() + "," + colNm + ")";
        //        strInsert = strInsert + "VALUES (" + dsMax.Tables[0].Rows[0][0] + "," + colVl + ")";

        //        JavaScriptSerializer js = new JavaScriptSerializer();

        //        Util.WritInfoLog(ClsName, "AddData", "Query for AddData", string.Format("Query:{0}", strInsert));
        //        Int32 res = sqlConn.SaveDataByFactory(strInsert);
        //        Util.WritInfoLog(ClsName, "AddData", "End Query for AddData");

        //        //if (res == 1) return "true";
        //        //else return "false";

        //        if (res == 1) return dsMax.Tables[0].Rows[0][0].ToString();
        //        else return "false";

        //        //return res.ToString();
        //        // return js.Serialize(new Records(sqlConn.ExecuteCommand(strInsert)));

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "AddData", "Error in AddData", "", ex);
        //        throw ex;
        //    }
        //}

        public string formDropdownString(string dropval)
        {

            try
            {
                string strVal = string.Empty;
                string[] arr = dropval.Split(',');

                for (int i = 0; i < arr.Length; i++)
                {
                    strVal = strVal + arr[i] + ":" + arr[i] + ";";
                }

                strVal = strVal.Substring(0, strVal.Length - 1);
                return strVal;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "formDropdownString", "Error in formDropdownString", "", ex);
                throw ex;
            }
        }

        //to get selected dimension master and x_ref data + gray data
        public string GetMasterData()
        {
            try
            {

                Util.WritInfoLog(ClsName, "GetMasterData", "Start Creating Connection from Common");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "GetMasterData", "End Creating Connection from Common");

                DimensionMaster dimmaster = new DimensionMaster();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));
                lstparam.Add(new DBParameter("@MasterId", DbType.String, ParameterDirection.Input, 255, MasterId));

                Util.WritInfoLog(ClsName, "GetMasterData", "method for GetMasterData");
                DataSet dsDim = sqlConn.GetDataSetFromFactoryProc("usp_GetMasterSourceData", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetMasterData", "End method for GetMasterData");

                DataTable dt = dsDim.Tables[0];

                //original before SP
                ////
                //string strColname = string.Empty;
                //string strColnameEx = string.Empty;

                ////field name of dimension
                //Util.WritInfoLog(ClsName, "GetMasterData", "Start Query for getting Column List", string.Format("Parameters: List{0}", ListId));
                //string strQry = " SELECT DISPLAY,FIELD_NAME,DATA_TYPE,PRECISION,IS_REQUIRED,DROPDOWN_VAL,CONTROL_TYPE,IS_PRIMARY ";
                //strQry = strQry + " FROM X_MASTER_LIST_COLUMNS WHERE LIST_ID =" + ListId;

                ////Table name of dimension
                //strQry = strQry + " SELECT TABLE_NAME,TABLE_XREF,TABLE_GREY,TABLE_HISTORY FROM X_MASTER_LIST ";
                //strQry = strQry + " WHERE LIST_ID= " + ListId.ToString();

                //Util.WritInfoLog(ClsName, "GetMasterData", "Query to be executed", string.Format("Query:{0}", strQry));
                //DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
                //Util.WritInfoLog(ClsName, "GetMasterData", "End Query for getting Column List");

                //DataTable dt = ds.Tables[0];
                //DataView dv = new DataView(dt);
                //dv.RowFilter = " IS_PRIMARY=True";

                ////get table names of dimension
                //string strTableName = string.Empty;
                //string strTableName_xref = string.Empty;
                //string strTableName_gray = string.Empty;
                //string strTableName_hist = string.Empty;

                //if (ds.Tables[1].Rows.Count > 0)
                //{
                //    strTableName = ds.Tables[1].Rows[0]["TABLE_NAME"].ToString();
                //    strTableName_xref = ds.Tables[1].Rows[0]["TABLE_XREF"].ToString();
                //    strTableName_gray = ds.Tables[1].Rows[0]["TABLE_GREY"].ToString();
                //    strTableName_hist = ds.Tables[1].Rows[0]["TABLE_HISTORY"].ToString();
                //}

                ////get attribute colname
                //foreach (DataRow dr in ds.Tables[0].Rows)
                //{
                //    if (strColname == "") { strColname = dr["FIELD_NAME"].ToString(); }
                //    else { strColname = strColname + "," + dr["FIELD_NAME"].ToString(); }
                //    if (dr["IS_PRIMARY"].ToString().ToUpper() == "FALSE")
                //    {
                //        if (strColnameEx == "") { strColnameEx = dr["FIELD_NAME"].ToString(); }
                //        else { strColnameEx = strColnameEx + "," + dr["FIELD_NAME"].ToString(); }
                //    }
                //    // strColnameHist
                //}

                //strColnameEx = strColnameEx + " ,MASTER_ID ";

                ////get master table data
                //string strQryDim = " SELECT TOP 1 " + strColname + " FROM " + strTableName + " WHERE " + dv[0]["FIELD_NAME"];
                //strQryDim = strQryDim + "=" + MasterId;

                ////get source table data
                //strQryDim = strQryDim + " SELECT " + strColnameEx + ",SOURCE ,XREF_ID FROM " + strTableName_xref + " WHERE MASTER_ID=";
                //strQryDim = strQryDim + MasterId;

                ////get gray table data
                //strQryDim = strQryDim + " SELECT " + strColnameEx + ",SOURCE,GREY_ID FROM " + strTableName_gray + " WHERE MASTER_ID=";
                //strQryDim = strQryDim + MasterId;

                ////get history table data
                //strQryDim = strQryDim + " SELECT TOP 1 " + strColnameEx + ",HIST_ID FROM " + strTableName_hist + " WHERE MASTER_ID=";
                //strQryDim = strQryDim + MasterId;
                //strQryDim = strQryDim + " ORDER BY MOD_DATE DESC";

                //Util.WritInfoLog(ClsName, "GetMasterData", "Query to be executed", string.Format("Query:{0}", strQryDim));
                //DataSet dsDim = sqlConn.GetDataSetFromFactory(strQryDim);
                //Util.WritInfoLog(ClsName, "GetMasterData", "End Query execution");
                //////

                DataTable DtDim = dsDim.Tables[1];
                DataTable DtDim_XREF = dsDim.Tables[2];
                //DataTable transposedTable_xref = GenerateTransposedTable(dsDim.Tables[1]);
                // DataTable transposedTable_xref = GenerateTransposedTableXref(dsDim.Tables[1], dsDim.Tables[3]);
                DataTable transposedTable_gray = GenerateTransposedTable(dsDim.Tables[3]);
                // DataTable transposedTable_hist = GenerateTransposedTable(dsDim.Tables[4]);

                //heading list 
                //List<string> dimheading = new List<string>();
                List<dimheading> dimheading = new List<dimheading>();

                dimheading dimhead = new dimheading();
                dimhead.text = "Fields";
                dimhead.colspan = "1";
                dimheading.Add(dimhead);

                dimheading dimheadmast = new dimheading();
                dimheadmast.text = "Master Value";
                dimheadmast.id = "thm" + MasterId.ToString();
                dimheadmast.val = MasterId.ToString();
                dimheadmast.colspan = "2";
                dimheading.Add(dimheadmast);

                int index = 0;

                foreach (DataRow dr in dsDim.Tables[2].Rows)
                {
                    dimheading dimheadx = new dimheading();
                    dimheadx.text = dr["Source"].ToString();
                    dimheadx.id = "thxref_" + index.ToString();//Convert.ToInt32(dr["XREF_ID"]) ;
                    dimheadx.val = Convert.ToString(dr["XREF_ID"]);
                    dimheadx.colspan = "1";
                    dimheading.Add(dimheadx);
                    index++;
                }

                foreach (DataRow dr in dsDim.Tables[3].Rows)
                {
                    dimheading dimheadg = new dimheading();
                    dimheadg.text = dr["Source"].ToString();
                    dimheadg.id = "grey" + "0";// Convert.ToString(dr["GREY_ID"]);
                    dimheadg.val = Convert.ToString(dr["GREY_ID"]);
                    dimheading.Add(dimheadg);
                    //dimheading.Add(dr["Source"].ToString());
                }

                //upper heading
                List<th> dimUpperHeading = new List<th>();

                th th = new th();
                th.colspan = 3;
                th.text = "Master";

                th th_xref = new th();
                th_xref.colspan = dsDim.Tables[2].Rows.Count;
                th_xref.text = "Source";

                th th_possible = new th();
                th_possible.colspan = dsDim.Tables[3].Rows.Count;
                th_possible.text = "Possible";

                dimUpperHeading.Add(th);
                dimUpperHeading.Add(th_xref);
                dimUpperHeading.Add(th_possible);

                //DataView dv_xref = new DataView(dsDim.Tables[1]); 
                //dv_xref.RowFilter=

                //string[]  arrhead = (from DataRow dr in dt.Rows where (string)dr.== "Source"); 

                //DataView Dv_xref = new DataView(transposedTable);
                //List<DataRow> xrefC_val = new List<DataRow>();
                //List<object> xrefC_vall = new List<object>();

                // transposedTable.Rows[0].
                //xrefC_vall =(List<object>) DtDim_XREF.Rows[0];

                int cnt = 0;
                List<DimensionMFields> listDimFlds = new List<DimensionMFields>();

                //
                DataView dvCol = new DataView(dt);
                dvCol.RowFilter = "IS_PRIMARY=FALSE";

                //foreach (DataRow dr in ds.Tables[0].Rows)
                foreach (DataRowView dr in dvCol)
                {


                    DimensionMFields dimField = new DimensionMFields();
                    dimField.name = dr["DISPLAY"].ToString();

                    if (DtDim.Rows.Count > 0)
                    {
                        dimField.value = DtDim.Rows[0][dr["FIELD_NAME"].ToString()].ToString();
                    }

                    int xref_id = 0;
                    if (dsDim.Tables[3].Rows.Count > 0)
                    {
                        // xref_id = Convert.ToInt32(dsDim.Tables[3].Rows[0][dr["FIELD_NAME"].ToString()]);
                        xref_id = Convert.ToInt32(dsDim.Tables[2].Rows[0]["XREF_ID"]);
                    }

                    dimField.xref_id = xref_id;

                    //object[] xrefArr_value = transposedTable_xref.Rows[cnt].ItemArray;
                    object[] grayArr_value = transposedTable_gray.Rows[cnt].ItemArray;

                    //getlist of xref objects column-wise
                    List<xref> lstxref = new List<xref>();
                    lstxref = GenerateXrefArray(dsDim.Tables[2], dsDim.Tables[4], dr["FIELD_NAME"].ToString());

                    dimField.xrefArr_value = lstxref;
                    dimField.grayArr_value = grayArr_value;

                    dimField.is_req = dr["IS_REQUIRED"].ToString();
                    dimField.datatype = dr["DATA_TYPE"].ToString();
                    dimField.prec = dr["PRECISION"].ToString();
                    dimField.control_type = dr["CONTROL_TYPE"].ToString();
                    dimField.control_name = dr["FIELD_NAME"].ToString();

                    //for control type dropdown
                    if (dr["DROPDOWN_VAL"].ToString() != "")
                    {

                        List<DropdownListItem> listFieldDrpitems = new List<DropdownListItem>();

                        string[] arrDrpval = dr["DROPDOWN_VAL"].ToString().Split(',');
                        //add default select 
                        DropdownListItem DropdownListItemd = new DropdownListItem();
                        DropdownListItemd.Text = "--select--";
                        DropdownListItemd.Value = "";
                        listFieldDrpitems.Add(DropdownListItemd);

                        for (int i = 0; i < arrDrpval.Length; i++)
                        {

                            DropdownListItem DropdownListItem = new DropdownListItem();
                            DropdownListItem.Text = arrDrpval[i];
                            DropdownListItem.Value = arrDrpval[i];

                            if (arrDrpval[i] == DropdownListItem.Value)
                            {
                                DropdownListItem.selected = "true";
                            }
                            else
                            {
                                DropdownListItem.selected = "false";
                            }

                            listFieldDrpitems.Add(DropdownListItem);
                        }

                        dimField.fielddrp_item = listFieldDrpitems;
                    }

                    listDimFlds.Add(dimField);
                    cnt++;
                }

                DimensionMaster Dimmast = new DimensionMaster();
                Dimmast.DimMFields = listDimFlds;
                Dimmast.dimheading = dimheading;
                Dimmast.dimUpperHeading = dimUpperHeading;

                //foreach (DataRow row in DtDim_XREF.Rows)
                //{
                //   // xref_val.Add(row[dr["FIELD_NAME"].ToString()]);
                //    //object obj=row[dr["FIELD_NAME"].ToString()];

                //}               

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(Dimmast);
                //return js.Serialize(listDimFlds);

            }
            catch (SqlException exs)
            {
                Util.WritErrorLog(ClsName, "GetMasterData", "SQL Exceptions Error in GetMasterData", "", exs);
                return "";
                throw exs;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetMasterData", "Error in GetMasterData", "", ex);
                return "";
                throw ex;
            }
        }

        private DataTable GenerateTransposedTable(DataTable inputTable)
        {

            DataTable outputTable = new DataTable();
            // Add columns by looping rows

            // Header row's first column is same as in inputTable
            //outputTable.Columns.Add(inputTable.Columns[0].ColumnName.ToString());

            // Header row's second column onwards, 'inputTable's first column taken
            foreach (DataRow inRow in inputTable.Rows)
            {
                string newColName = inRow[0].ToString();
                outputTable.Columns.Add(newColName);
            }
            // Add rows by looping columns    
            //if (inputTable.Rows.Count > 0)
            //{
            for (int rCount = 0; rCount <= inputTable.Columns.Count - 1; rCount++)
            {

                DataRow newRow = outputTable.NewRow();
                // First column is inputTable's Header row's second column
                //newRow[0] = inputTable.Columns[rCount].ColumnName.ToString();

                for (int cCount = 0; cCount <= inputTable.Rows.Count - 1; cCount++)
                {
                    string colValue = inputTable.Rows[cCount][rCount].ToString();
                    // newRow[cCount + 1] = colValue;
                    newRow[cCount] = colValue;
                }

                outputTable.Rows.Add(newRow);
            }
            //}
            return outputTable;

        }

        //testing purpose
        private List<xref> GenerateXrefArray(DataTable XrefTable, DataTable HistTable, string colname)
        {

            try
            {

                List<xref> lstXref = new List<xref>();

                foreach (DataRow inRow in XrefTable.Rows)
                {

                    xref xref = new xref();
                    xref.value = inRow[colname].ToString();

                    if (HistTable.Rows.Count > 0)
                    {
                        if (Convert.ToInt32(HistTable.Rows[0][colname].ToString().Trim()) == Convert.ToInt32(inRow["XREF_ID"].ToString()))
                        {
                            xref.bsource = true;
                        }
                    }
                    else
                    {
                        xref.bsource = false;
                    }

                    lstXref.Add(xref);
                }

                return lstXref;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetMasterData", "Error in GetMasterData", "", ex);
                throw ex;
            }

        }

        //private DataTable GenerateTransposedTable(DataTable inputTable)
        //{

        //    DataTable outputTable = new DataTable();

        //    // Add columns by looping rows

        //    // Header row's first column is same as in inputTable
        //    outputTable.Columns.Add(inputTable.Columns[0].ColumnName.ToString());

        //    // Header row's second column onwards, 'inputTable's first column taken
        //    foreach (DataRow inRow in inputTable.Rows)
        //    {
        //        string newColName = inRow[0].ToString();
        //        outputTable.Columns.Add(newColName);
        //    }

        //    // Add rows by looping columns        
        //    for (int rCount = 1; rCount <= inputTable.Columns.Count - 1; rCount++)
        //    {
        //        DataRow newRow = outputTable.NewRow();

        //        // First column is inputTable's Header row's second column
        //        newRow[0] = inputTable.Columns[rCount].ColumnName.ToString();
        //        for (int cCount = 0; cCount <= inputTable.Rows.Count - 1; cCount++)
        //        {
        //            string colValue = inputTable.Rows[cCount][rCount].ToString();
        //            newRow[cCount + 1] = colValue;
        //        }

        //        outputTable.Rows.Add(newRow);
        //    }

        //    return outputTable;
        //}

        public string GetGriddataURL(Boolean bdata)
        {
            try
            {

                string primarykey = string.Empty;
                Grid griddata = new Grid();
                string strData = string.Empty;
                Connection sqlConn = new Connection();

                //original before SP

                //string strQry = " SELECT * FROM X_MASTER_LIST_COLUMNS ";
                //strQry = strQry + " WHERE LIST_ID= " + ListId;      

                //Util.WritInfoLog(ClsName, "GetGriddataURL", "Query for getting column list in GetGriddataURL", string.Format("Query:{0}", strQry));
                //DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
                //Util.WritInfoLog(ClsName, "GetGriddataURL", "End Query for getting column list in GetGriddataURL");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));

                Util.WritInfoLog(ClsName, "GetGriddataURL", "method for usp_GetDimensionDataByListId");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetDimensionDataByListId", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetGriddataURL", "End method for usp_GetDimensionDataByListId");

                DataTable dt = ds.Tables[0];

                if (bdata == false)
                {

                    List<colModel> listColmodel = new List<colModel>();
                    List<string> columns = new List<string>();

                    int index = 0;
                    //
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {

                        //for columns
                        columns.Add(dr["DISPLAY"].ToString());

                        //for colmodel
                        colModel colmodel = new colModel();

                        colmodel.index = dr["FIELD_NAME"].ToString();
                        colmodel.name = dr["FIELD_NAME"].ToString();
                        colmodel.width = 150;

                        Dictionary<string, object> edoptions = new Dictionary<string, object>();
                        Dictionary<string, object> editrules = new Dictionary<string, object>();
                        Dictionary<string, object> formatoptions = new Dictionary<string, object>();

                        if (Convert.ToString(dr["PRECISION"]) != "")
                        {
                            //edoptions.Add("size", Convert.ToInt32(dr["PRECISION"]));
                            //edoptions.Add("width", Convert.ToInt32(dr["PRECISION"]));
                            edoptions.Add("maxlength", Convert.ToInt32(dr["PRECISION"]));

                        }

                        if (Convert.ToString(dr["IS_REQUIRED"]).ToLower() == "true")
                        {
                            editrules.Add("required", true);
                        }

                        if (dr["IS_PRIMARY"].ToString().ToLower() == "true")
                        {
                            primarykey = dr["FIELD_NAME"].ToString();
                            //colmodel.editable = false;//sorttype:'int'
                            //colmodel.sorttype = "int";//depending on datatype do it from X_MASTER_LIST_COLUMNS
                            // Dictionary<string, Boolean> edoption = new Dictionary<string, Boolean>();
                            //editrules:{required:true, edithidden:true}, 
                            //colmodel.hiddden = true;
                            //colmodel.editoptions = edoptions;

                            edoptions.Add("required", true);
                            edoptions.Add("edithidden", true);
                            colmodel.key = true;
                            colmodel.editable = false;
                            //editrules.Add("edithidden", true); 
                        }
                        else
                        {
                            colmodel.editable = true;
                        }

                        if (dr["DATA_TYPE"].ToString().ToLower() == "number") //integer for whole number
                        {
                            colmodel.sorttype = "int";
                            colmodel.align = "right";

                            editrules.Add("number", true);
                            edoptions.Add("NullIfEmpty", true);
                            //colmodel.NullIfEmpty = "true";                        
                            //formatter 
                            colmodel.formatter = "number";
                            //format option for number
                            formatoptions.Add("decimalSeparator", ".");
                            formatoptions.Add("defaulValue", 0);
                            formatoptions.Add("decimalPlaces", 2);
                        }
                        else if (dr["DATA_TYPE"].ToString().ToLower() == "varchar")
                        {
                            colmodel.sorttype = "string";
                            colmodel.align = "left";

                        }
                        else if (dr["DATA_TYPE"].ToString().ToLower() == "date")
                        {
                            colmodel.sorttype = "date";
                            colmodel.align = "right";

                            //edoptions.Add("dataInit","function(el) {  $(el).datepicker({ dateFormat: 'mm-dd-yy' });");
                            //edoptions.Add("dataInit"," $(el).datepicker();");
                            editrules.Add("date", true);
                            colmodel.formatter = "date";
                            ////date format in mm/dd/yyyy
                            formatoptions.Add("srcformat", "ISO8601Long");
                            formatoptions.Add("newformat", "m/d/Y");
                            //edoptions.Add("type", "date");
                            //  edoptions.Add("editoptions", "js:function(elm){}");                         
                            // "editoptions"=>array("editoptions"=> 
                            //"js:function(elm){setTimeout(function(){ 
                            //    jQuery(elm).datepicker({dateFormat:'yy-mm-dd'}); 
                            //    jQuery('.ui-datepicker').css({'font-size':'75%'}); 
                            //},200);}") 
                        }
                        else if (dr["DATA_TYPE"].ToString().ToLower() == "float")
                        {
                            colmodel.sorttype = "float";
                            colmodel.align = "right";
                        }

                        //control type
                        if (dr["CONTROL_TYPE"].ToString().ToLower() == "textarea")
                        {
                            colmodel.edittype = dr["CONTROL_TYPE"].ToString().ToLower();
                            edoptions.Add("rows", "2");
                            edoptions.Add("cols", "20");
                        }
                        else if (dr["CONTROL_TYPE"].ToString().ToLower() == "checkbox")
                        {
                            colmodel.edittype = "checkbox";//,editoptions:{value:"Yes:No"} 
                            edoptions.Add("value", "Yes:No");
                            colmodel.formatter = "checkbox";
                        }
                        else if (dr["CONTROL_TYPE"].ToString().ToLower() == "dropdown")
                        {
                            colmodel.edittype = "select";//editoptions:{value:"FE:FedEx;TN:TNT"}
                            //colmodel.formatter = "select";
                            string drpval = formDropdownString(dr["DROPDOWN_VAL"].ToString());
                            edoptions.Add("value", drpval); //values in this format
                            edoptions.Add("style", "width:160px");

                            // edoptions.Add("value", "0:Select;FE:FedEx;TN:TNT"); //values in this format
                            //edoptions.Add("size", 400);
                        }

                        colmodel.editoptions = edoptions;
                        colmodel.editrules = editrules;
                        colmodel.formatoptions = formatoptions;

                        listColmodel.Add(colmodel);
                        index++;
                    }

                    griddata.colModelList = listColmodel;
                    griddata.columns = columns;

                }
                else
                {
                    //foreach (DataRow dr in ds.Tables[1].Rows)
                    //{
                    //}
                    DataTable dtcol = ds.Tables[0];
                    DataView dv = new DataView(dtcol);

                    dv.RowFilter = " IS_PRIMARY =TRUE";
                    if (sidx == "")
                    {
                        sidx = dv[0]["FIELD_NAME"].ToString();
                    }

                    string sortExp = sidx + " " + sord;
                    int start = ((page - 1) * rows);


                    string strQryy = " SELECT  * FROM ";
                    strQryy = strQryy + " (SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + ") AS Row,";
                    strQryy = strQryy + " * FROM  " + dimTableName + ") tmp ";
                    strQryy = strQryy + " WHERE Row > " + start + " AND Row <= " + (start + rows);

                    strQryy = strQryy + " SELECT * FROM  " + dimTableName;

                    Util.WritInfoLog(ClsName, "GetGriddataURL", "Query for GetGriddataURL", string.Format("Query:{0}", strQryy));
                    DataSet dsdata = sqlConn.GetDataSetFromFactory(strQryy);
                    Util.WritInfoLog(ClsName, "GetGriddataURL", "End Query for GetGriddataURL");

                    //do for paging 
                    DataTable dtData = dsdata.Tables[0];
                    int cnt = 0;
                    cnt = Convert.ToInt32(dsdata.Tables[1].Rows.Count);
                    int total_pages = 0;
                    // int limit=rows;
                    //int limit = rows;
                    if (cnt > 0)
                    {
                        total_pages = cnt / rows;
                    }
                    else
                    {
                        total_pages = 0;
                    }

                    string test = sidx;
                    if (page > total_pages) page = total_pages;
                    // int start = limit * page - limit; // do not put $limit*($page - 1)              

                    //SELECT  NODE_ID, HIERARCHY_ID FROM     
                    //(SELECT  ROW_NUMBER() OVER (ORDER BY NODE_ID ASC) AS Row,
                    //    NODE_ID, HIERARCHY_ID FROM  C_PRD_NODES) tmp 
                    //    WHERE   Row > 20 AND Row <= 30
                    // DataSet dsData = sqlConn.GetDataSetFromFactory(strData);
                    // DataTable dtData = ds.Tables[0];                   
                    //List<object> rows = new List<object>();

                    //foreach (DataRow dr in dt.Rows)
                    //{

                    //    Dictionary<string, object> row = new Dictionary<string, object>();
                    //    foreach (DataColumn dc in dt.Columns)
                    //    {
                    //        row.Add(dc.ColumnName, dr[dc].ToString());
                    //    }
                    //    rows.Add(row);
                    //}
                    //griddata.datarow = rows;
                    //griddata.primarykey = primarykey;

                    strData = Common.JsonForJqgrid(dtData, rows, cnt, page);
                    // return str;
                }

                JavaScriptSerializer js = new JavaScriptSerializer();

                if (bdata == true)
                {
                    // return js.Serialize(strData);
                    return strData;
                }
                else
                {
                    return js.Serialize(griddata);
                }

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetGriddataURL", "Error in GetGriddataURL", "", ex);
                throw ex;
            }
        }

        //save changed source 
        public string SaveChangedSource()
        {
            try
            {

                Connection sqlConn = new Connection();

                JavaScriptSerializer js1 = new JavaScriptSerializer();
                List<SrcColumns> listCols1 = js1.Deserialize<List<SrcColumns>>(srcVal);

                var xmlDocument = new XmlDocument();
                var serializer = new XmlSerializer(typeof(List<SrcColumns>));

                using (var stream = new MemoryStream())
                {
                    serializer.Serialize(stream, listCols1);
                    stream.Flush(); stream.Seek(0, SeekOrigin.Begin);
                    xmlDocument.Load(stream);
                }

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@field_Xml", DbType.Xml, ParameterDirection.Input, 0, xmlDocument.OuterXml));
                lstparam.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));
                lstparam.Add(new DBParameter("@MasterId", DbType.String, ParameterDirection.Input, 0, MasterId));

                Util.WritInfoLog(ClsName, "GetGriddata", "method for EditData");
                Int32 res = Convert.ToInt32(sqlConn.SaveDataByFactoryByProc("usp_SaveChangedSource", ref(lstparam)));
                Util.WritInfoLog(ClsName, "GetGriddata", "End method for EditData");

                return "true";

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "SaveChangedSource", "Error in SaveChangedSource", "", ex);
                return "false";
                throw ex;
            }
        }


        //original before SP
        ////save changed source 
        //public string SaveChangedSource()
        //{
        //    try
        //    {

        //        Connection sqlConn = new Connection();

        //        //field name of dimension
        //        string strQry = " SELECT DISPLAY,FIELD_NAME,DATA_TYPE,PRECISION,IS_REQUIRED,DROPDOWN_VAL,CONTROL_TYPE";
        //        strQry = strQry + ",IS_PRIMARY FROM X_MASTER_LIST_COLUMNS WHERE LIST_ID =" + ListId;

        //        //Table name of dimension
        //        strQry = strQry + " SELECT TABLE_NAME,TABLE_XREF,TABLE_GREY,TABLE_HISTORY FROM X_MASTER_LIST ";
        //        strQry = strQry + " WHERE LIST_ID= " + ListId.ToString();

        //        Util.WritInfoLog(ClsName, "SaveChangedSource", "Query for getting details in SaveChangedSource", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "SaveChangedSource", "End Query for getting details in SaveChangedSource");

        //        DataTable dt = ds.Tables[0];
        //        DataView dv = new DataView(dt);
        //        dv.RowFilter = " IS_PRIMARY=True";

        //        //get table names of dimension      
        //        string strTableName = string.Empty;
        //        string strTableName_xref = string.Empty;
        //        string strTableName_hist = string.Empty;

        //        if (ds.Tables[1].Rows.Count > 0)
        //        {
        //            strTableName = ds.Tables[1].Rows[0]["TABLE_NAME"].ToString();
        //            strTableName_xref = ds.Tables[1].Rows[0]["TABLE_XREF"].ToString();
        //            strTableName_hist = ds.Tables[1].Rows[0]["TABLE_HISTORY"].ToString();
        //        }

        //        //get source table data
        //        string strQryDim = " SELECT * FROM " + strTableName_xref + " WHERE MASTER_ID=";
        //        strQryDim = strQryDim + MasterId;

        //        strQryDim = strQryDim + " SELECT MAX(HIST_ID)+ 1 AS MAX_ID FROM " + strTableName_hist;

        //        Util.WritInfoLog(ClsName, "SaveChangedSource", "Query for getting x_ref details", string.Format("Query:{0}", strQryDim));
        //        DataSet dsDim = sqlConn.GetDataSetFromFactory(strQryDim);
        //        Util.WritInfoLog(ClsName, "SaveChangedSource", "End Query for getting x_ref details");

        //        DataTable Dtxref = dsDim.Tables[0];
        //        DataView Dvxref = new DataView(Dtxref);

        //        JavaScriptSerializer js = new JavaScriptSerializer();
        //        List<Columns> listCols = js.Deserialize<List<Columns>>(srcVal);

        //        ////update end_date to row which we have to save
        //        //string strQryUpd = " UPDATE " + strTableName + " SET END_DATE = '" + DateTime.Now.AddDays(-1) + "'";
        //        ////strQryUpd = strQryUpd + " WHERE NODE_ID = " + NodeId.ToString() + " AND END_DATE >= '" + DateTime.Now.ToString("MM/dd/yyyy") + "'";
        //        //strQryUpd = strQryUpd + " WHERE START_DATE = ( SELECT TOP(1) START_DATE FROM " + strTableName;
        //        //strQryUpd = strQryUpd + " WHERE NODE_ID = " + NodeId.ToString() + " AND  CONVERT(DATE,END_DATE,101) >= '" + DateTime.Now.ToString("MM/dd/yyyy") + "'";
        //        //strQryUpd = strQryUpd + " ORDER BY START_DATE DESC )";

        //        //Int32 ret = sqlConn.SaveDataByFactory(strQryUpd);

        //        //save in history
        //        string strInsert = "INSERT INTO " + strTableName_hist;

        //        string colNm = string.Empty;
        //        string colVl = string.Empty;
        //        string strUdd = string.Empty;

        //        DataView DvType = new DataView(dt);

        //        foreach (Columns colElem in listCols)
        //        {

        //            DvType.RowFilter = "FIELD_NAME ='" + colElem.colname + "'"; //ATTR_DATA_TYPE
        //            //if (DvType[0]["DATA_TYPE"].ToString().ToUpper() == "VARCHAR" || DvType[0]["ATTR_DATA_TYPE"].ToString().ToUpper() == "DATE")
        //            //{
        //            //    colVl = colVl + "'" + colElem.colvalue + "'" + ",";//ATTR_COLUMN
        //            //}
        //            //else if (DvType[0]["DATA_TYPE"].ToString().ToUpper() == "NUMBER")
        //            //{
        //            //    colVl = colVl + colElem.colvalue + ",";
        //            //}

        //            //value is number
        //            colVl = colVl + colElem.colvalue + ",";
        //            colNm = colNm + DvType[0]["FIELD_NAME"].ToString() + ",";
        //        }

        //        colNm = colNm.Substring(0, colNm.Length - 1);
        //        colVl = colVl.Substring(0, colVl.Length - 1);

        //        strInsert = strInsert + "(HIST_ID,MASTER_ID,MOD_DATE," + colNm + ")";
        //        strInsert = strInsert + "VALUES (" + dsDim.Tables[1].Rows[0]["MAX_ID"] + "," + MasterId + ",'";
        //        strInsert = strInsert + DateTime.Now + "'," + colVl + ")";

        //        Int32 ret2 = sqlConn.SaveDataByFactory(strInsert);

        //        ////update master table 
        //        string strQryUpd = " UPDATE " + strTableName + " SET ";// +" SET END_DATE = '" + DateTime.Now.AddDays(-1) + "'";

        //        foreach (Columns colElem in listCols)
        //        {

        //            DvType.RowFilter = "FIELD_NAME ='" + colElem.colname + "'"; //ATTR_DATA_TYPE
        //            Dvxref.RowFilter = "XREF_ID=" + colElem.colvalue;

        //            if (DvType[0]["IS_PRIMARY"].ToString().ToUpper() == "FALSE")
        //            {
        //                if (DvType[0]["DATA_TYPE"].ToString().ToUpper() == "VARCHAR" || DvType[0]["ATTR_DATA_TYPE"].ToString().ToUpper() == "DATE")
        //                {
        //                    strUdd = strUdd + DvType[0]["FIELD_NAME"].ToString() + "='" + Dvxref[0][colElem.colname].ToString() + "'" + ",";
        //                }
        //                else if (DvType[0]["DATA_TYPE"].ToString().ToUpper() == "NUMBER")
        //                {
        //                    strUdd = strUdd + DvType[0]["FIELD_NAME"].ToString() + "=" + Dvxref[0][colElem.colname].ToString() + ",";
        //                }
        //                //strUdd = strUdd + DvType[0]["FIELD_NAME"].ToString() + "=" + colElem.colvalue + ",";
        //            }
        //        }

        //        strUdd = strUdd.Substring(0, strUdd.Length - 1);
        //        strQryUpd = strQryUpd + strUdd + " WHERE " + dv[0]["FIELD_NAME"] + "= " + MasterId.ToString();

        //        Util.WritInfoLog(ClsName, "SaveChangedSource", "Query for update in SaveChangedSource", string.Format("Query:{0}", strQryUpd));
        //        Int32 ret = sqlConn.SaveDataByFactory(strQryUpd);
        //        Util.WritInfoLog(ClsName, "SaveChangedSource", "End Query for update in SaveChangedSource");

        //        return "true";

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "SaveChangedSource", "Error in SaveChangedSource", "", ex);
        //        return "false";
        //        throw ex;
        //    }
        //}

        //import Data
        public string ImportData()
        {
            try
            {

                Util.WritInfoLog(ClsName, "ImportData", "Start Creating Connection from Common");
                Connection Conn = new Connection();
                Util.WritInfoLog(ClsName, "ImportData", "End Creating Connection from Common");

                //check if sheetname exists                
                Util.WritInfoLog(ClsName, "ImportData", "method to be executed for Excel sheet name", string.Format("Query:{0}", "GetSheetNameFromExcel"));
                DataTable dtSheetExcel = Conn.GetSheetNameFromExcel(strfilePath);
                Util.WritInfoLog(ClsName, "ImportData", "method to be executed for Excel sheet name");

                strSheetName = strSheetName + "$";
                Boolean bSheetExist = false;

                foreach (DataRow dr in dtSheetExcel.Rows)
                {
                    if (dr[2].ToString().Trim().ToLower() == strSheetName.Trim().ToLower())
                    {
                        bSheetExist = true;
                        break;
                    }
                }

                if (bSheetExist == true)
                {

                    string strQryEx = " SELECT * FROM [" + strSheetName + "] ";

                    Util.WritInfoLog(ClsName, "ImportData", "Query to be executed for Excel", string.Format("Query:{0}", strQryEx));
                    DataSet dsExcel = Conn.GetDataSetFromExcel(strQryEx, strfilePath);
                    Util.WritInfoLog(ClsName, "ImportData", "End Query to be executed for Excel");
                    DataTable tempXref = dsExcel.Tables[0];

                    //original before SP
                    //string strQry = " SELECT X_MASTER_LIST.*,X_MASTER_LIST_COLUMNS.* FROM X_MASTER_LIST";
                    //strQry = strQry + " INNER JOIN X_MASTER_LIST_COLUMNS ";
                    //strQry = strQry + " ON X_MASTER_LIST.LIST_ID=X_MASTER_LIST_COLUMNS.LIST_ID ";
                    //strQry = strQry + " WHERE X_MASTER_LIST.LIST_ID=" + ListId;

                    //strQry = strQry + " SELECT TABLE_XREF_TEMP FROM X_MASTER_LIST WHERE X_MASTER_LIST.LIST_ID=" + ListId;

                    //Util.WritInfoLog(ClsName, "ImportData", "Query for getting columns of dimension", string.Format("Query:{0}", strQry));
                    //DataSet ds1 = Conn.GetDataSetFromFactory(strQry);
                    //Util.WritInfoLog(ClsName, "ImportData", "End Query for getting columns of dimension");

                    List<DBParameter> lstparam1 = new List<DBParameter>();
                    lstparam1.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));

                    Util.WritInfoLog(ClsName, "ImportData", "method for getting columns of dimension");
                    DataSet ds1 = Conn.GetDataSetFromFactoryProc("usp_GetDimensionTempXrefName", ref(lstparam1));
                    Util.WritInfoLog(ClsName, "ImportData", "End method for getting columns of dimension");

                    DataTable dt = ds1.Tables[0];
                    ConfigData config = new ConfigData();

                    DataView dvcol = new DataView(dt);
                    dvcol.RowFilter = "IS_PRIMARY ='FALSE' OR IS_PRIMARY IS NULL";

                    DataTable dtcol = dvcol.ToTable();
                    string[] arraycol = dtcol.Rows.Cast<DataRow>()
                                .Select(row => row["FIELD_NAME"].ToString())
                                    .ToArray();

                    string[] arraycolxref = (from DataColumn x in tempXref.Columns
                                             where (x.ColumnName.ToString().Trim().ToLower() != "source" && x.ColumnName.ToString().Trim().ToLower() != "source_id")
                                             select x.ColumnName).ToArray();
                    Boolean bmap = true;
                    //column mapping
                    if (arraycol.Length != arraycolxref.Length)
                    {
                        bmap = false;
                    }
                    else
                    {
                        for (int i = 0; i < arraycol.Length; i++)
                        {
                            if (arraycol[i].Trim() != arraycolxref[i].Trim())
                            {
                                bmap = false;
                                break;
                            }
                        }
                    }

                    if (bmap == true)
                    {


                        HMSPrincipal principal;
                        principal = (HMSPrincipal)Thread.CurrentPrincipal;
                        HMSIdentity userIdentity = principal.identity;

                        Int32 userid = userIdentity.userPK;

                        //save in import details
                        List<DBParameter> lstparam = new List<DBParameter>();
                        lstparam.Add(new DBParameter("@DIM_ID", DbType.Int16, ParameterDirection.Input, 0, ListId));
                        lstparam.Add(new DBParameter("@EXCEL_FILE_NAME", DbType.String, ParameterDirection.Input, 256, strfileName));
                        lstparam.Add(new DBParameter("@CREATED_BY", DbType.Int16, ParameterDirection.Input, 0, userid));//user_id
                        lstparam.Add(new DBParameter("@IMP_ID", DbType.Int16, ParameterDirection.Output, 0, 0));//user_id

                        Util.WritInfoLog(ClsName, "ImportData", "procedure for saving import details", "usp_SaveImportDetails");
                        int ret = Conn.SaveDataByFactoryByProc("usp_SaveImportDetails", ref(lstparam));
                        Util.WritInfoLog(ClsName, "ImportData", "End procedure for saving import details");

                        int IMP_ID = 0;

                        foreach (DBParameter dbparam in lstparam)
                        {
                            if (dbparam.name == "@IMP_ID")
                            {
                                IMP_ID = Convert.ToInt32(dbparam.value);
                                break;
                            }
                        }

                        DataColumn dcol = new DataColumn("IMP_ID", typeof(System.Int32));
                        tempXref.Columns.Add(dcol);

                        //set imp_id in temp_xref
                        foreach (DataRow dr in tempXref.Rows)
                        {
                            dr["IMP_ID"] = IMP_ID;
                        }

                        string strSelect = " SELECT * FROM " + ds1.Tables[1].Rows[0]["TABLE_XREF_TEMP"] + " WHERE 1=0 ";

                        Util.WritInfoLog(ClsName, "ImportData", "method for bulk insert from excel to relational db", string.Format("Query:{0}", strSelect));
                        Boolean bflag = Conn.BatchUpdatedbWithAdapter(strSelect, tempXref, 50);
                        Util.WritInfoLog(ClsName, "ImportData", "End method for bulk insert from excel to relational db");


                        Util.WritInfoLog(ClsName, "ImportData", "method for getting columns of grid for match and unmatch result", string.Format("Query:{0}", ""));
                        string strdata = GetMatchRuleColumns(false);
                        Util.WritInfoLog(ClsName, "ImportData", "End method for getting columns of grid for match and unmatch result");

                        return strdata + "~" + IMP_ID;
                    }
                    else
                    {
                        return "notmapped";
                    }
                }
                else
                {
                    return "noSheet";
                }
            }
            catch (SqlException exs)
            {
                Util.WritErrorLog(ClsName, "ImportData", "SQL Exceptions Error in ImportData", "", exs);
                return "false";
                throw exs;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "ImportData", "Error in ImportData", "", ex);
                return "false";
                throw ex;
            }
        }

        public string GetMatchRuleColumns(Boolean bCheckbox)
        {
            try
            {

                string primarykey = string.Empty;

                Connection sqlConn = new Connection();
                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@List_ID", DbType.Int16, ParameterDirection.Input, 0, ListId));

                Util.WritInfoLog(ClsName, "GetMatchRuleColumns", "procedure for getting  _xref_temp columns", "usp_GetTempXref");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetTempXref", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetMatchRuleColumns", "End procedure for getting  _xref_temp columns");

                Grid griddata = new Grid();
                Common common = new Common();

                DataTable dt = ds.Tables[0];
                dt.Rows[2]["IS_PRIMARY"] = 0; //for xref_id
                dt.Rows[0]["IS_PRIMARY"] = 1;//for row
                dt.Rows[2]["IS_REQUIRED"] = 1;//for xref_id

                if (bCheckbox == false)
                {
                    dt.Rows.RemoveAt(1);
                }

                foreach (DataRowView dr in dt.DefaultView)
                {
                    if (dr["FIELD_NAME"].ToString() == "XREF_ID" || dr["FIELD_NAME"].ToString() == "DIM_ID" || dr["FIELD_NAME"].ToString() == "Id" || dr["FIELD_NAME"].ToString() == "SOURCE_ID" || dr["FIELD_NAME"].ToString() == "MASTER_ID" || dr["FIELD_NAME"].ToString() == "IMP_ID")
                    //if ( dr["FIELD_NAME"].ToString() == "DIM_ID" || dr["FIELD_NAME"].ToString() == "Id" || dr["FIELD_NAME"].ToString() == "SOURCE_ID" || dr["FIELD_NAME"].ToString() == "MASTER_ID" || dr["FIELD_NAME"].ToString() == "IMP_ID")
                    {
                        dr["IS_HIDDEN"] = 1;
                    }
                }

                //set columns,colmodel,primary key               
                Util.WritInfoLog(ClsName, "GetMatchRuleColumns", "SetColModel for GetMatchRuleColumns", "");
                griddata = common.SetColModel(griddata, ds.Tables[0]);
                Util.WritInfoLog(ClsName, "GetMatchRuleColumns", "End SetColModel for GetMatchRuleColumns");

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(griddata);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetMatchRuleColumns", "Error in GetMatchRuleColumns", "", ex);
                throw ex;
            }
        }

        public string GetListColumns(bool bPrimary)
        {

            string strCols = string.Empty;

            try
            {
                Connection sqlConn = new Connection();
                if (RuleId == 0) RuleId = 1;

                string strQryPrim = " SELECT * FROM X_MASTER_LIST_COLUMNS ";
                strQryPrim = strQryPrim + " WHERE LIST_ID =" + ListId;

                if (bPrimary == false)
                {
                    strQryPrim = strQryPrim + " AND IS_PRIMARY ='False'";
                }

                Util.WritInfoLog(ClsName, "GetListColumns", "Query for GetListColumns", string.Format("Query:{0}", strQryPrim));
                DataSet dscol = sqlConn.GetDataSetFromFactory(strQryPrim);
                Util.WritInfoLog(ClsName, "GetListColumns", "End Query for GetListColumns");

                DataTable dtcol = dscol.Tables[0];
                StringBuilder SB = new StringBuilder();

                foreach (DataRowView dr in dtcol.DefaultView)
                {
                    SB.Append(dr["FIELD_NAME"].ToString() + ", ");
                }
                strCols = SB.ToString();
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetListColumns", "Error in GetListColumns", "", ex);
                throw ex;
            }
            return strCols;
        }

        public string GetMatchRuledata()
        {
            try
            {

                Connection sqlConn = new Connection();
                if (RuleId == 0) RuleId = 1;

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));
                //for search filter
                if (_search && (query != null || query != ""))
                {
                    lstparam.Add(new DBParameter("@query", DbType.String, ParameterDirection.Input, 0, query.Replace("\"", "'")));
                }
                lstparam.Add(new DBParameter("@RuleId", DbType.Int32, ParameterDirection.Input, 0, RuleId));
                lstparam.Add(new DBParameter("@Imp_Id", DbType.Int32, ParameterDirection.Input, 0, Imp_Id));
                lstparam.Add(new DBParameter("@dimTableName", DbType.String, ParameterDirection.Input, 0, dimTableName));
                lstparam.Add(new DBParameter("@_search", DbType.Boolean, ParameterDirection.Input, 0, _search));

                lstparam.Add(new DBParameter("@singleMatch", DbType.Boolean, ParameterDirection.Input, 0, 1));

                Util.WritInfoLog(ClsName, "GetMatchRuledata", "procedure for match rule data", "usp_GetMatchRuledata");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetMatchRuledata", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetMatchRuledata", "End procedure for match rule data");

                DataTable dt = ds.Tables[0];
                dt.Columns.Remove("priority");

                int cnt = 0;
                //cnt = Convert.ToInt32(ds.Tables[1].Rows[0]["CNT"]);
                cnt = Convert.ToInt32(ds.Tables[0].Rows.Count);
                rows = cnt;
                string strData = Common.JsonForJqgrid(dt, rows, cnt, page);

                return strData;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetMatchRuledata", "Error in GetMatchRuledata", "", ex);
                throw ex;
            }
        }

        //original before SP
        //public string GetMatchRuledata()
        //{
        //    try
        //    {

        //        Connection sqlConn = new Connection();
        //        if (RuleId == 0) RuleId = 1;

        //        string strQryPrim = " SELECT * FROM X_MASTER_LIST_COLUMNS ";
        //        strQryPrim = strQryPrim + " WHERE LIST_ID =" + ListId;

        //        strQryPrim = strQryPrim + " SELECT TABLE_NAME,TABLE_XREF,TABLE_XREF_TEMP,TABLE_XREF_TEMPDETAIL FROM X_MASTER_LIST ";
        //        strQryPrim = strQryPrim + " WHERE LIST_ID =" + ListId;

        //        Util.WritInfoLog(ClsName, "GetMatchRuledata", "Query for dimension columns and _xref temp tables name", string.Format("Query:{0}", strQryPrim));
        //        DataSet dscol = sqlConn.GetDataSetFromFactory(strQryPrim);
        //        Util.WritInfoLog(ClsName, "GetMatchRuledata", "End Query for dimension columns and _xref temp tables name");

        //        DataTable dtcol = dscol.Tables[0];
        //        StringBuilder SB = new StringBuilder();
        //        StringBuilder SBGold = new StringBuilder();

        //        string TABLE_XREF_TEMP = dscol.Tables[1].Rows[0]["TABLE_XREF_TEMP"].ToString();
        //        string TABLE_XREF_TEMP_DETAIL = dscol.Tables[1].Rows[0]["TABLE_XREF_TEMPDETAIL"].ToString();

        //        DataView dvcol1 = dtcol.DefaultView;
        //        dvcol1.RowFilter = "IS_PRIMARY ='FALSE'";

        //        foreach (DataRowView dr in dvcol1)
        //        {
        //            SB.Append(dr["FIELD_NAME"].ToString() + ", ");
        //            SBGold.Append("GOLD." + dr["FIELD_NAME"].ToString() + ", ");
        //        }

        //        string strCols = SB.ToString();
        //        string strGoldCols = SBGold.ToString();

        //        DataView dvcol = dtcol.DefaultView;
        //        dvcol.RowFilter = "IS_PRIMARY ='TRUE'";
        //        string strPrimary = dvcol[0]["FIELD_NAME"].ToString();

        //        if (sidx == "" || sidx == null)
        //        {
        //            //sidx = strPrimary;
        //            sidx = " XREF_ID ";
        //        }


        //        string sortExp = sidx + " " + sord;
        //        int start = ((page - 1) * rows);

        //        string strQry = " SELECT ROW_NUMBER() ";
        //        strQry = strQry + " OVER (ORDER BY XREF_ID , priority ) AS Row,* ";
        //        strQry = strQry + " FROM ( (";
        //        strQry = strQry + " SELECT 0 as priority ,temp_xref.XREF_ID,";

        //        strQry = strQry + strCols;

        //        strQry = strQry + " SOURCE,SOURCE_ID,temp_xref.MASTER_ID,temp_id.Rule_Id 'MANUAL_RULE_ID',temp_xref.IMP_ID ";
        //        strQry = strQry + " FROM  " + TABLE_XREF_TEMP + " temp_xref ";
        //        strQry = strQry + " INNER JOIN " + TABLE_XREF_TEMP_DETAIL + " temp_id ";
        //        strQry = strQry + " ON temp_xref.XREF_ID=temp_id.Xref_Id ";
        //        strQry = strQry + " WHERE temp_xref.IMP_ID= " + Imp_Id;           
        //        //single match logic
        //        //  not exist in temp detail table 
        //        strQry = strQry + " AND EXISTS ( ";
        //        strQry = strQry + " SELECT XREF_ID FROM " + TABLE_XREF_TEMP_DETAIL + "  temp_id ";
        //        strQry = strQry + " where Rule_Id = " + RuleId;
        //        strQry = strQry + " AND temp_id.Xref_Id =temp_Xref.XREF_ID ";
        //        strQry = strQry + " GROUP BY Xref_Id ";
        //        strQry = strQry + " HAVING COUNT(XREF_ID) =1 ) ";

        //        strQry = strQry + " UNION ";

        //        strQry = strQry + "  SELECT 1 as priority ,temp_xref.xref_id,";
        //        strQry = strQry + strGoldCols;
        //        strQry = strQry + " '' SOURCE,'' SOURCE_ID,'' MASTER_ID,'' MANUAL_RULE_ID,temp_id.IMP_ID FROM " + dimTableName;
        //        strQry = strQry + " GOLD  INNER JOIN  " + TABLE_XREF_TEMP_DETAIL + " temp_id ";
        //        strQry = strQry + " ON GOLD.DIM_ID =temp_id.MASTER_ID ";
        //        strQry = strQry + " INNER JOIN  " + TABLE_XREF_TEMP + " temp_xref ";
        //        strQry = strQry + " ON temp_id.Xref_Id =temp_xref.XREF_ID ";            
        //        strQry = strQry + " WHERE temp_xref.IMP_ID= " + Imp_Id;

        //        //  not exist in temp detail table 
        //        strQry = strQry + " AND EXISTS ( ";
        //        strQry = strQry + " SELECT XREF_ID FROM " + TABLE_XREF_TEMP_DETAIL + " temp_id ";
        //        strQry = strQry + " where Rule_Id = " + RuleId;
        //        strQry = strQry + " AND temp_id.Xref_Id =temp_Xref.XREF_ID ";
        //        strQry = strQry + " GROUP BY Xref_Id ";
        //        strQry = strQry + " HAVING COUNT(XREF_ID) =1 ) ";

        //        strQry = strQry + " ))tmp ";
        //        //for search filter
        //        if (_search && (query != null || query != ""))
        //        {
        //            strQry = strQry + " WHERE " + query.Replace("\"", "'") + ";";
        //        }


        //        //strQry = strQry + " SELECT COUNT(*) CNT FROM( ";
        //        //strQry = strQry + " SELECT ROW_NUMBER() ";
        //        //strQry = strQry + " OVER (ORDER BY XREF_ID , priority ) AS Row,* ";
        //        //strQry = strQry + " FROM ( (";
        //        //strQry = strQry + " SELECT 0 as priority ,XREF_ID,";

        //        ////strQry = strQry + " contact_id,first_name,last_name,";
        //        //strQry = strQry + strCols;

        //        //strQry = strQry + " SOURCE,SOURCE_ID,MASTER_ID,MANUAL_RULE_ID ";
        //        //strQry = strQry + " FROM  " + TABLE_XREF_TEMP + "  temp_xref ";
        //        //strQry = strQry + " WHERE temp_xref.IMP_ID= " + Imp_Id;
        //        ////for search filter
        //        //if (_search && (query != null || query != ""))
        //        //{
        //        //    strQry = strQry + " AND " + query.Replace("\"", "'");//errString.Replace("docment", "document");
        //        //}
        //        //// strQry = strQry + " where MASTER_ID !=0  ";
        //        ////strQry = strQry + " AND MANUAL_RULE_ID = " + RuleId; //put rule id
        //        //strQry = strQry + " AND EXISTS ( ";
        //        //strQry = strQry + " SELECT XREF_ID FROM " + TABLE_XREF_TEMP_DETAIL + " temp_id ";
        //        //strQry = strQry + " where Rule_Id = " + RuleId;
        //        //strQry = strQry + " AND temp_id.Xref_Id =temp_Xref.XREF_ID ";
        //        //strQry = strQry + " GROUP BY Xref_Id ";
        //        //strQry = strQry + " HAVING COUNT(XREF_ID) =1 ) ";

        //        //strQry = strQry + " UNION ";
        //        ////original
        //        ////strQry = strQry + "  SELECT 1 as priority ,temp_xref.xref_id,";
        //        //////strQry = strQry + " GOLD.contact_id,GOLD.first_name,GOLD.last_name,";
        //        ////strQry = strQry + strGoldCols;
        //        ////strQry = strQry + " '' SOURCE,'' SOURCE_ID,'' MASTER_ID,'' MANUAL_RULE_ID FROM " + dimTableName;
        //        ////strQry = strQry + " GOLD  INNER JOIN  temp_xref  ON ";
        //        ////strQry = strQry + "GOLD." + strPrimary + " =temp_xref.MASTER_ID ";
        //        ////strQry = strQry + " where MASTER_ID !=0  ";
        //        ////strQry = strQry + " AND  MANUAL_RULE_ID = " + RuleId; //put rule id
        //        ////strQry = strQry + " ))tmp ";
        //        ////strQry = strQry + " ) temp ;";
        //        /////
        //        //strQry = strQry + "  SELECT 1 as priority ,temp_xref.xref_id,";
        //        //strQry = strQry + strGoldCols;
        //        //strQry = strQry + " '' SOURCE,'' SOURCE_ID,'' MASTER_ID,'' MANUAL_RULE_ID FROM " + dimTableName;
        //        //strQry = strQry + " GOLD  INNER JOIN  " + TABLE_XREF_TEMP_DETAIL + " temp_id ";
        //        //strQry = strQry + " ON GOLD.DIM_ID =temp_id.MASTER_ID ";
        //        //strQry = strQry + " INNER JOIN " + TABLE_XREF_TEMP + "  temp_xref ";
        //        //strQry = strQry + " ON temp_id.Xref_Id =temp_xref.XREF_ID ";
        //        ////strQry = strQry + " where temp_xref.MASTER_ID !=0  ";
        //        //// strQry = strQry + " AND  temp_id.Rule_Id = " + RuleId; //put rule id
        //        //strQry = strQry + " WHERE  temp_id.Rule_Id = " + RuleId; //put rule id
        //        //strQry = strQry + " AND temp_id.IMP_ID= " + Imp_Id;
        //        ////for search filter
        //        //if (_search && (query != null || query != ""))
        //        //{
        //        //    strQry = strQry + " AND temp_xref." + query.Replace("\"", "'");//errString.Replace("docment", "document");
        //        //}
        //        //strQry = strQry + " ))tmp) temp ;";

        //        Util.WritInfoLog(ClsName, "GetMatchRuledata", "Query for match rule data", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "GetMatchRuledata", "End Query for match rule data");

        //        DataTable dt = ds.Tables[0];
        //        //dt.re
        //        // dt.Columns.Remove("Row");
        //        dt.Columns.Remove("priority");

        //        int cnt = 0;
        //        //cnt = Convert.ToInt32(ds.Tables[1].Rows[0]["CNT"]);
        //        cnt = Convert.ToInt32(ds.Tables[0].Rows.Count);
        //        string strData = Common.JsonForJqgrid(dt, rows, cnt, page);


        //        return strData;
        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "GetMatchRuledata", "Error in GetMatchRuledata", "", ex);
        //        throw ex;
        //    }
        //}

        public string GetManualRuledata()
        {
            try
            {

                Connection sqlConn = new Connection();
                if (RuleId == 0) RuleId = 1;

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));
                //for search filter
                if (_search && (query != null || query != ""))
                {
                    lstparam.Add(new DBParameter("@query", DbType.String, ParameterDirection.Input, 0, query.Replace("\"", "'")));
                }
                lstparam.Add(new DBParameter("@RuleId", DbType.Int32, ParameterDirection.Input, 0, RuleId));
                lstparam.Add(new DBParameter("@Imp_Id", DbType.Int32, ParameterDirection.Input, 0, Imp_Id));
                lstparam.Add(new DBParameter("@dimTableName", DbType.String, ParameterDirection.Input, 0, dimTableName));
                lstparam.Add(new DBParameter("@_search", DbType.Boolean, ParameterDirection.Input, 0, _search));

                lstparam.Add(new DBParameter("@singleMatch", DbType.Boolean, ParameterDirection.Input, 0, 0));

                Util.WritInfoLog(ClsName, "GetMatchRuledata", "procedure for match rule data", "usp_GetMatchRuledata");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetMatchRuledata", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetMatchRuledata", "End procedure for match rule data");

                DataTable dt = ds.Tables[0];
                dt.Columns.Remove("priority");

                int cnt = 0;
                //cnt = Convert.ToInt32(ds.Tables[1].Rows[0]["CNT"]);
                cnt = Convert.ToInt32(ds.Tables[0].Rows.Count);
                rows = cnt;
                string strData = Common.JsonForJqgrid(dt, rows, cnt, page);

                return strData;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetManualRuledata", "Error in GetManualRuledata", "", ex);
                throw ex;
            }
        }

        //original before SP
        //public string GetManualRuledata()
        //{
        //    try
        //    {

        //        Connection sqlConn = new Connection();
        //        if (RuleId == 0) RuleId = 1;

        //        string strQryPrim = " SELECT * FROM X_MASTER_LIST_COLUMNS ";
        //        strQryPrim = strQryPrim + " WHERE LIST_ID =" + ListId;

        //        strQryPrim = strQryPrim + " SELECT TABLE_NAME,TABLE_XREF,TABLE_XREF_TEMP,TABLE_XREF_TEMPDETAIL FROM X_MASTER_LIST ";
        //        strQryPrim = strQryPrim + " WHERE LIST_ID =" + ListId;

        //        Util.WritInfoLog(ClsName, "GetManualRuledata", "Query for dimension columns and _xref temp tables name", string.Format("Query:{0}", strQryPrim));
        //        DataSet dscol = sqlConn.GetDataSetFromFactory(strQryPrim);
        //        Util.WritInfoLog(ClsName, "GetManualRuledata", "End Query for dimension columns and _xref temp tables name");

        //        DataTable dtcol = dscol.Tables[0];
        //        StringBuilder SB = new StringBuilder();
        //        StringBuilder SBGold = new StringBuilder();

        //        string TABLE_XREF_TEMP = dscol.Tables[1].Rows[0]["TABLE_XREF_TEMP"].ToString();
        //        string TABLE_XREF_TEMP_DETAIL = dscol.Tables[1].Rows[0]["TABLE_XREF_TEMPDETAIL"].ToString();

        //        DataView dvcol1 = dtcol.DefaultView;
        //        dvcol1.RowFilter = "IS_PRIMARY ='FALSE'";

        //        foreach (DataRowView dr in dvcol1)
        //        {
        //            SB.Append(dr["FIELD_NAME"].ToString() + ", ");
        //            SBGold.Append("GOLD." + dr["FIELD_NAME"].ToString() + ", ");
        //        }

        //        string strCols = SB.ToString();
        //        string strGoldCols = SBGold.ToString();

        //        DataView dvcol = dtcol.DefaultView;
        //        dvcol.RowFilter = "IS_PRIMARY ='TRUE'";
        //        string strPrimary = dvcol[0]["FIELD_NAME"].ToString();

        //        if (sidx == "" || sidx == null)
        //        {
        //            sidx = "XREF_ID";
        //            //sidx = strPrimary;
        //        }


        //        string sortExp = sidx + " " + sord;
        //        int start = ((page - 1) * rows);

        //        string strQry = " SELECT ROW_NUMBER() ";
        //        strQry = strQry + " OVER (ORDER BY XREF_ID , priority ) AS Row,'' checkbox,* ";
        //        strQry = strQry + " FROM ( (";
        //        strQry = strQry + " SELECT DISTINCT 0 as priority ,temp_xref.XREF_ID,";

        //        strQry = strQry + strCols;

        //        strQry = strQry + " SOURCE,SOURCE_ID,temp_xref.MASTER_ID,temp_id.Rule_Id 'MANUAL_RULE_ID',temp_xref.IMP_ID ";
        //        strQry = strQry + " FROM  " + TABLE_XREF_TEMP + " temp_xref ";
        //        strQry = strQry + " INNER JOIN  " + TABLE_XREF_TEMP_DETAIL + " temp_id ";
        //        strQry = strQry + " ON temp_xref.XREF_ID=temp_id.Xref_Id ";

        //        //strQry = strQry + " where MASTER_ID !=0  ";
        //        //strQry = strQry + " AND MANUAL_RULE_ID = " + RuleId; //put rule id
        //        ////multiple match logic
        //        ////  not exist in temp detail table 
        //        //strQry = strQry + " AND EXISTS ( ";
        //        strQry = strQry + " WHERE temp_xref.IMP_ID= " + Imp_Id;
        //        //multiple match logic
        //        //  not exist in temp detail table 
        //        strQry = strQry + " AND EXISTS ( ";

        //        strQry = strQry + " SELECT XREF_ID FROM  " + TABLE_XREF_TEMP_DETAIL + " temp_id ";
        //        strQry = strQry + " where Rule_Id = " + RuleId;
        //        strQry = strQry + " AND temp_id.Xref_Id =temp_Xref.XREF_ID ";
        //        strQry = strQry + " GROUP BY Xref_Id ";
        //        strQry = strQry + " HAVING COUNT(XREF_ID) > 1 ) ";

        //        strQry = strQry + " UNION ALL ";

        //        //strQry = strQry + "  SELECT 1 as priority ,temp_xref.xref_id,";
        //        //strQry = strQry + strGoldCols;
        //        //strQry = strQry + " '' SOURCE,'' SOURCE_ID,'' MASTER_ID,'' MANUAL_RULE_ID FROM " + dimTableName;
        //        //strQry = strQry + " GOLD  INNER JOIN  temp_xref  ON ";
        //        //strQry = strQry + "GOLD." + strPrimary + " =temp_xref.MASTER_ID ";
        //        //strQry = strQry + " where MASTER_ID !=0  ";
        //        //strQry = strQry + " AND  MANUAL_RULE_ID = " + RuleId; //put rule id
        //        //strQry = strQry + " ))tmp ;";

        //        strQry = strQry + "  SELECT 1 as priority ,temp_xref.xref_id,";
        //        strQry = strQry + strGoldCols;
        //        strQry = strQry + " '' SOURCE,'' SOURCE_ID,GOLD.DIM_ID MASTER_ID,'' MANUAL_RULE_ID,temp_id.IMP_ID FROM " + dimTableName;
        //        strQry = strQry + " GOLD  INNER JOIN  " + TABLE_XREF_TEMP_DETAIL + " temp_id ";
        //        strQry = strQry + " ON GOLD.DIM_ID =temp_id.MASTER_ID ";
        //        strQry = strQry + " INNER JOIN   " + TABLE_XREF_TEMP + " temp_xref ";
        //        strQry = strQry + " ON temp_id.Xref_Id =temp_xref.XREF_ID ";

        //        //strQry = strQry + " where temp_xref.MASTER_ID !=0  ";
        //        //strQry = strQry + " AND  MANUAL_RULE_ID = " + RuleId; //put rule id
        //        ////multiple match logic
        //        ////  not exist in temp detail table 
        //        //strQry = strQry + " AND EXISTS ( ";
        //        strQry = strQry + " WHERE temp_id.IMP_ID= " + Imp_Id;
        //        //multiple match logic
        //        //  not exist in temp detail table 
        //        strQry = strQry + " AND EXISTS ( ";

        //        strQry = strQry + " SELECT XREF_ID FROM " + TABLE_XREF_TEMP_DETAIL + " temp_id ";
        //        strQry = strQry + " where Rule_Id = " + RuleId;
        //        strQry = strQry + " AND temp_id.Xref_Id =temp_Xref.XREF_ID ";
        //        strQry = strQry + " GROUP BY Xref_Id ";
        //        strQry = strQry + " HAVING COUNT(XREF_ID) > 1 ) ";

        //        strQry = strQry + " ))tmp ";
        //        //for search filter
        //        if (_search && (query != null || query != ""))
        //        {
        //            strQry = strQry + " WHERE " + query.Replace("\"", "'") + ";";//errString.Replace("docment", "document");
        //        }


        //        ////strQry = strQry + " SELECT COUNT(*) CNT FROM( ";
        //        ////strQry = strQry + " SELECT ROW_NUMBER() ";
        //        ////strQry = strQry + " OVER (ORDER BY XREF_ID , priority ) AS Row,* ";
        //        ////strQry = strQry + " FROM ( (";
        //        ////strQry = strQry + " SELECT 0 as priority ,XREF_ID,";

        //        //////strQry = strQry + " contact_id,first_name,last_name,";
        //        ////strQry = strQry + strCols;

        //        ////strQry = strQry + " SOURCE,SOURCE_ID,MASTER_ID,MANUAL_RULE_ID ";
        //        ////strQry = strQry + " FROM  " + TABLE_XREF_TEMP + " temp_xref ";
        //        ////strQry = strQry + " WHERE temp_xref.IMP_ID= " + Imp_Id;
        //        //////strQry = strQry + " where MASTER_ID !=0  ";
        //        //////strQry = strQry + " AND MANUAL_RULE_ID = " + RuleId; //put rule id
        //        ////strQry = strQry + " AND EXISTS ( ";

        //        ////strQry = strQry + " SELECT XREF_ID FROM temp_id ";
        //        ////strQry = strQry + " where Rule_Id = " + RuleId;
        //        ////strQry = strQry + " AND temp_id.Xref_Id =temp_Xref.XREF_ID ";
        //        ////strQry = strQry + " GROUP BY Xref_Id ";
        //        ////strQry = strQry + " HAVING COUNT(XREF_ID) > 1 ) ";

        //        ////strQry = strQry + " UNION ";
        //        //////original
        //        //////strQry = strQry + "  SELECT 1 as priority ,temp_xref.xref_id,";
        //        ////////strQry = strQry + " GOLD.contact_id,GOLD.first_name,GOLD.last_name,";
        //        //////strQry = strQry + strGoldCols;
        //        //////strQry = strQry + " '' SOURCE,'' SOURCE_ID,'' MASTER_ID,'' MANUAL_RULE_ID FROM " + dimTableName;
        //        //////strQry = strQry + " GOLD  INNER JOIN  temp_xref  ON ";
        //        //////strQry = strQry + "GOLD." + strPrimary + " =temp_xref.MASTER_ID ";
        //        //////strQry = strQry + " where MASTER_ID !=0  ";
        //        //////strQry = strQry + " AND  MANUAL_RULE_ID = " + RuleId; //put rule id
        //        //////strQry = strQry + " ))tmp ";
        //        //////strQry = strQry + " ) temp ;";
        //        ///////
        //        ////strQry = strQry + "  SELECT 1 as priority ,temp_xref.xref_id,";
        //        ////strQry = strQry + strGoldCols;
        //        ////strQry = strQry + " '' SOURCE,'' SOURCE_ID,'' MASTER_ID,'' MANUAL_RULE_ID FROM " + dimTableName;
        //        ////strQry = strQry + " GOLD  INNER JOIN  " + TABLE_XREF_TEMP_DETAIL + " temp_id ";
        //        ////strQry = strQry + " ON GOLD.DIM_ID =temp_id.MASTER_ID ";
        //        ////strQry = strQry + " INNER JOIN  " + TABLE_XREF_TEMP + " temp_xref ";
        //        ////strQry = strQry + " ON temp_id.Xref_Id =temp_xref.XREF_ID ";
        //        //////strQry = strQry + " where temp_xref.MASTER_ID !=0  ";
        //        ////// strQry = strQry + " AND  temp_id.Rule_Id = " + RuleId; //put rule id
        //        ////strQry = strQry + " WHERE  temp_id.Rule_Id = " + RuleId; //put rule id
        //        ////strQry = strQry + " ))tmp) temp ;";


        //        Util.WritInfoLog(ClsName, "GetManualRuledata", "Query for manual rule data", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "GetManualRuledata", "End Query for manual rule data");

        //        DataTable dt = ds.Tables[0];
        //        //dt.re
        //        // dt.Columns.Remove("Row");
        //        dt.Columns.Remove("priority");

        //        int cnt = 0;
        //        // cnt = Convert.ToInt32(ds.Tables[1].Rows[0]["CNT"]);
        //        cnt = dt.Rows.Count;
        //        string strData = Common.JsonForJqgrid(dt, rows, cnt, page);

        //        return strData;
        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "GetManualRuledata", "Error in GetManualRuledata", "", ex);
        //        throw ex;
        //    }
        //}

        public string GetUnMatchRuledata()
        {
            try
            {

                Connection sqlConn = new Connection();
                if (RuleId == 0) RuleId = 1;

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));
                //for search filter
                if (_search && (query != null || query != ""))
                {
                    lstparam.Add(new DBParameter("@query", DbType.String, ParameterDirection.Input, 0, query.Replace("\"", "'")));
                }

                lstparam.Add(new DBParameter("@RuleId", DbType.Int32, ParameterDirection.Input, 0, RuleId));
                lstparam.Add(new DBParameter("@Imp_Id", DbType.Int32, ParameterDirection.Input, 0, Imp_Id));
                lstparam.Add(new DBParameter("@dimTableName", DbType.String, ParameterDirection.Input, 0, dimTableName));
                lstparam.Add(new DBParameter("@_search", DbType.Boolean, ParameterDirection.Input, 0, _search));

                Util.WritInfoLog(ClsName, "GetMatchRuledata", "procedure for match rule data", "usp_GetMatchRuledata");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetManualRuledata", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetMatchRuledata", "End procedure for match rule data");

                DataTable dt = ds.Tables[0];
                dt.Columns.Remove("priority");

                int cnt = 0;
                //cnt = Convert.ToInt32(ds.Tables[1].Rows[0]["CNT"]);
                cnt = Convert.ToInt32(ds.Tables[0].Rows.Count);
                rows = cnt;
                string strData = Common.JsonForJqgrid(dt, rows, cnt, page);
                return strData;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetUnMatchRuledata", "Error in GetUnMatchRuledata", "", ex);
                throw ex;
            }
        }

        //original before SP
        //public string GetUnMatchRuledata()
        //{
        //    try
        //    {

        //        Connection sqlConn = new Connection();

        //        string strQryPrim = " SELECT * FROM X_MASTER_LIST_COLUMNS ";
        //        strQryPrim = strQryPrim + " WHERE LIST_ID =" + ListId;

        //        strQryPrim = strQryPrim + " SELECT TABLE_NAME,TABLE_XREF,TABLE_XREF_TEMP,TABLE_XREF_TEMPDETAIL FROM X_MASTER_LIST ";
        //        strQryPrim = strQryPrim + " WHERE LIST_ID =" + ListId;

        //        Util.WritInfoLog(ClsName, "GetUnMatchRuledata", "Query for dimension columns and _xref temp tables name", string.Format("Query:{0}", strQryPrim));
        //        DataSet dscol = sqlConn.GetDataSetFromFactory(strQryPrim);
        //        Util.WritInfoLog(ClsName, "GetUnMatchRuledata", "End Query for dimension columns and _xref temp tables name");

        //        DataTable dtcol = dscol.Tables[0];
        //        StringBuilder SB = new StringBuilder();
        //        StringBuilder SBGold = new StringBuilder();

        //        string TABLE_XREF_TEMP = dscol.Tables[1].Rows[0]["TABLE_XREF_TEMP"].ToString();
        //        string TABLE_XREF_TEMP_DETAIL = dscol.Tables[1].Rows[0]["TABLE_XREF_TEMPDETAIL"].ToString();

        //        DataView dvcol1 = dtcol.DefaultView;
        //        dvcol1.RowFilter = "IS_PRIMARY ='FALSE'";

        //        foreach (DataRowView dr in dvcol1)
        //        {
        //            SB.Append(dr["FIELD_NAME"].ToString() + ", ");
        //            SBGold.Append("GOLD." + dr["FIELD_NAME"].ToString() + ", ");
        //        }

        //        string strCols = SB.ToString();
        //        string strGoldCols = SBGold.ToString();

        //        DataView dvcol = dtcol.DefaultView;
        //        dvcol.RowFilter = "IS_PRIMARY ='TRUE'";
        //        // string strPrimary = dscol.Tables[0].Rows[0]["FIELD_NAME"].ToString();
        //        string strPrimary = dvcol[0]["FIELD_NAME"].ToString();

        //        if (sidx == "" || sidx == null)
        //        {
        //            //sidx = strPrimary;
        //            sidx = "XREF_ID";
        //        }


        //        string sortExp = sidx + " " + sord;
        //        int start = ((page - 1) * rows);

        //        string strQry = " SELECT ROW_NUMBER() ";
        //        strQry = strQry + " OVER (ORDER BY XREF_ID , priority ) AS Row,* ";
        //        strQry = strQry + " FROM ( (";
        //        strQry = strQry + " SELECT 0 as priority ,temp_xref.XREF_ID,";

        //        //strQry = strQry + " contact_id,first_name,last_name,";
        //        strQry = strQry + strCols;
        //        strQry = strQry + " SOURCE,SOURCE_ID,temp_xref.MASTER_ID,temp_id.Rule_Id 'MANUAL_RULE_ID',temp_xref.IMP_ID ";
        //        strQry = strQry + " FROM  " + TABLE_XREF_TEMP + " temp_xref ";
        //        strQry = strQry + " LEFT JOIN " + TABLE_XREF_TEMP_DETAIL + " temp_id ";
        //        strQry = strQry + " ON temp_xref.XREF_ID=temp_id.Xref_Id ";


        //        //strQry = strQry + " where MASTER_ID =0  OR MASTER_ID IS NULL";
        //        ////  not exist in temp detail table 
        //        //strQry = strQry + "  AND NOT EXISTS (SELECT XREF_ID FROM temp_id ";
        //        strQry = strQry + " WHERE temp_xref.IMP_ID= " + Imp_Id;

        //        //  not exist in temp detail table 
        //        strQry = strQry + "  AND NOT EXISTS (SELECT XREF_ID FROM " + TABLE_XREF_TEMP_DETAIL + " temp_id ";
        //        strQry = strQry + " where temp_Xref.XREF_ID =temp_id.Xref_Id )  ";

        //        strQry = strQry + " ))tmp ";
        //        //for search filter
        //        if (_search && (query != null || query != ""))
        //        {
        //            strQry = strQry + " WHERE " + query.Replace("\"", "'") + ";";//errString.Replace("docment", "document");
        //        }

        //        //strQry = strQry + " SELECT COUNT(*) CNT FROM( ";
        //        //strQry = strQry + " SELECT ROW_NUMBER() ";
        //        //strQry = strQry + " OVER (ORDER BY DIM_ID , priority ) AS Row,* ";
        //        //strQry = strQry + " FROM ( (";
        //        //strQry = strQry + " SELECT 0 as priority ,XREF_ID,";
        //        ////strQry = strQry + " contact_id,first_name,last_name,";
        //        //strQry = strQry + strCols;
        //        //strQry = strQry + " SOURCE,SOURCE_ID,MASTER_ID,MANUAL_RULE_ID ";
        //        //strQry = strQry + " FROM  temp_xref ";
        //        ////strQry = strQry + " where MASTER_ID =0  OR MASTER_ID IS NULL";
        //        //////  not exist in temp detail table 
        //        ////strQry = strQry + "  AND NOT EXISTS (SELECT XREF_ID FROM temp_id ";

        //        ////  not exist in temp detail table 
        //        //strQry = strQry + "  WHERE NOT EXISTS (SELECT XREF_ID FROM temp_id ";
        //        //strQry = strQry + " where temp_Xref.XREF_ID =temp_id.Xref_Id )  ";

        //        //strQry = strQry + " ))tmp ";
        //        //strQry = strQry + " ) temp ;";

        //        Util.WritInfoLog(ClsName, "GetUnMatchRuledata", "Query for getting unmatch rule data", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "GetUnMatchRuledata", "End Query for getting unmatch rule data");

        //        DataTable dt = ds.Tables[0];
        //        //dt.re
        //        // dt.Columns.Remove("Row");
        //        dt.Columns.Remove("priority");

        //        int cnt = 0;
        //        //cnt = Convert.ToInt32(ds.Tables[1].Rows[0]["CNT"]);
        //        cnt = Convert.ToInt32(dt.Rows.Count);
        //        string strData = Common.JsonForJqgrid(dt, rows, cnt, page);


        //        return strData;
        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "GetUnMatchRuledata", "Error in GetUnMatchRuledata", "", ex);
        //        throw ex;
        //    }
        //}


        //currently not in use
        //public string GetSubGriddata()
        //{
        //    try
        //    {


        //        Connection sqlConn = new Connection();
        //        string strQryPrim = " SELECT * FROM X_MASTER_LIST_COLUMNS ";
        //        strQryPrim = strQryPrim + " WHERE LIST_ID =" + ListId;
        //        strQryPrim = strQryPrim + " AND IS_PRIMARY ='TRUE'";


        //        Util.WritInfoLog(ClsName, "GetMaster", "Query for GetMaster", string.Format("Query:{0}", strQryPrim));
        //        DataSet dsPrime = sqlConn.GetDataSetFromFactory(strQryPrim);
        //        Util.WritInfoLog(ClsName, "GetMaster", "End Query for GetMaster");

        //        string strPrimary = dsPrime.Tables[0].Rows[0]["FIELD_NAME"].ToString();

        //        string strQry = " SELECT '' xref_id,DIM_ID,first_name,last_name,'' SOURCE,'' ";
        //        strQry = strQry + " SOURCE_ID,'' MASTER_ID,'' MANUAL_RULE_ID FROM " + dimTableName;
        //        strQry = strQry + " WHERE " + strPrimary + "=" + MASTER_ID + ";";

        //        Util.WritInfoLog("$ConfigData$", "GetXMasterListdata", "Query for GetXMasterListdata", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog("$ConfigData$", "GetXMasterListdata", "End Query for GetXMasterListdata");

        //        DataTable dt = ds.Tables[0];

        //        int cnt = 0;
        //        page = 0;
        //        rows = 10;

        //        cnt = Convert.ToInt32(dt.Rows.Count);
        //        // string strData = Common.JsonForjqSubgrid(ds.Tables[0]);
        //        string strData = Common.JsonForJqgrid(ds.Tables[0], rows, cnt, page);
        //        return strData;
        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog("$ConfigData$", "GetXMasterListdata", "Error in GetXMasterListdata", "", ex);
        //        throw ex;
        //    }
        //}

        //match rule

        public string matchRule()
        {
            try
            {

                Util.WritInfoLog(ClsName, "matchRule", "Start Creating Connection from Common");
                Connection Conn = new Connection();
                Util.WritInfoLog(ClsName, "matchRule", "End Creating Connection from Common");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));
                lstparam.Add(new DBParameter("@Imp_Id", DbType.Int32, ParameterDirection.Input, 0, Imp_Id));

                Util.WritInfoLog(ClsName, "GetMatchRuledata", "procedure for getting rule data and dimension data", "usp_GetMatchRule");
                DataSet ds = Conn.GetDataSetFromFactoryProc("usp_matchRule", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetMatchRuledata", "End procedure for getting rule data and dimension data");

                ////original before procedure
                ////field name of dimension
                //string strQry = " SELECT * FROM vw_MatchRule ";
                //strQry = strQry + " WHERE LIST_ID =" + ListId;

                //strQry = strQry + " SELECT * FROM X_MASTER_LIST_COLUMNS ";
                //strQry = strQry + " WHERE LIST_ID =" + ListId;
                //strQry = strQry + " AND IS_PRIMARY ='TRUE' ;";

                //strQry = strQry + " SELECT TABLE_NAME,TABLE_XREF,TABLE_XREF_TEMP,TABLE_XREF_TEMPDETAIL FROM X_MASTER_LIST ";
                //strQry = strQry + " WHERE LIST_ID =" + ListId;

                //Util.WritInfoLog(ClsName, "matchRule", "Query for getting dimension details", string.Format("Query:{0}", strQry));
                //DataSet ds = Conn.GetDataSetFromFactory(strQry);
                //Util.WritInfoLog(ClsName, "matchRule", "End Query for getting dimension details");

                ////string strPrimary = ds.Tables[1].Rows[0]["FIELD_NAME"].ToString();

                //DataTable dt = ds.Tables[0];
                //DataTable dtDistinctRule = dt.DefaultView.ToTable(true, "MATCH_RULE_ID");

                ////string strTable = "temp_xref";
                //string TABLE_NAME = ds.Tables[2].Rows[0]["TABLE_NAME"].ToString();
                //string TABLE_XREF = ds.Tables[2].Rows[0]["TABLE_XREF"].ToString();
                //string TABLE_XREF_TEMP = ds.Tables[2].Rows[0]["TABLE_XREF_TEMP"].ToString();
                //string TABLE_XREF_TEMPDETAIL = ds.Tables[2].Rows[0]["TABLE_XREF_TEMPDETAIL"].ToString();

                //////query for dropping temp object
                ////string strDrop = " IF OBJECT_ID('tempdb..#temp_id') IS NOT NULL DROP TABLE #temp_id ; ";

                ////Util.WritInfoLog("$ReportData$", "GetGriddata", "Query for dropping temp table", string.Format("Query:{0}", strDrop));
                ////int ret = Conn.SaveDataByFactory(strDrop);
                ////Util.WritInfoLog("$ReportData$", "GetGriddata", "End Query for dropping temp table");
                //////temproray
                ////string strDel = " Delete from temp_id ; ";

                ////Util.WritInfoLog("$ReportData$", "GetGriddata", "Query for dropping temp table", string.Format("Query:{0}", strDel));
                ////ret = Conn.SaveDataByFactory(strDel);
                ////Util.WritInfoLog("$ReportData$", "GetGriddata", "End Query for dropping temp table");


                ////string strCreate = " CREATE TABLE #temp_id(Rule_Id int,Xref_Id int,Master_Id int); ";

                ////Util.WritInfoLog("$ReportData$", "GetGriddata", "Query for creating temp table", string.Format("Query:{0}", strCreate));
                ////ret = Conn.SaveDataByFactory(strCreate);
                ////Util.WritInfoLog("$ReportData$", "GetGriddata", "End Query for creating temp table");
                /////////////

                //foreach (DataRow row in dtDistinctRule.Rows) // Loop over the rules.
                //{

                //    int ruleid = Convert.ToInt16(row["MATCH_RULE_ID"]);
                //    DataView dvRule = dt.DefaultView;
                //    dvRule.RowFilter = "MATCH_RULE_ID =" + ruleid;

                //    Util.WritInfoLog(ClsName, "matchRule", "Query for getting rule condition ", string.Format("Rule:{0}", ruleid));
                //    string strWhere = GenerateMatchRule(dvRule.ToTable(),TABLE_XREF_TEMP);
                //    Util.WritInfoLog(ClsName, "matchRule", "End Query for getting rule condition ");

                //    //strWhere = strWhere + " AND ( temp_xref.MASTER_ID IS NULL AND temp_xref.MANUAL_RULE_ID IS NULL) ";
                //    strWhere = strWhere + " AND (( " + TABLE_XREF_TEMP + ".MASTER_ID=0 OR " + TABLE_XREF_TEMP + ".MASTER_ID IS NULL) ";
                //    strWhere = strWhere + " AND (" + TABLE_XREF_TEMP + ".MANUAL_RULE_ID=0 OR " + TABLE_XREF_TEMP + ".MANUAL_RULE_ID IS NULL)) ";

                //    //////remove later
                //    //////update master and rule id in temp_xref
                //    //string strUpdate = "  UPDATE temp_xref  SET temp_xref.MASTER_ID = Gold." + strPrimary;
                //    //strUpdate = strUpdate + " ,temp_xref.MANUAL_RULE_ID= " + ruleid;
                //    //strUpdate = strUpdate + "  FROM " + dimTableName + " Gold ";
                //    //strUpdate = strUpdate + " WHERE " + strWhere;

                //    //Util.WritInfoLog(ClsName, "matchRule", "method for updating master_id and rule_id in temp_xref ", string.Format("Query:{0}", strUpdate));
                //    //Int32 resUpd = Conn.SaveDataByFactory(strUpdate);
                //    //Util.WritInfoLog(ClsName, "matchRule", "End method for updating master_id and rule_id in temp_xref");

                //    /////////                 
                //    //strWhere = GenerateMatchRule(dvRule.ToTable(), TABLE_XREF_TEMP);

                //    string strDet = " Insert into " + TABLE_XREF_TEMPDETAIL + " (Rule_Id,Xref_Id,Master_Id,IMP_ID) Select '" + ruleid.ToString();
                //    strDet = strDet + "' Rule_Id,";
                //    strDet = strDet + TABLE_XREF_TEMP + ".XREF_ID, ";
                //    strDet = strDet + " Gold.DIM_ID," + TABLE_XREF_TEMP + ".IMP_ID ";
                //    strDet = strDet + " from  " + TABLE_XREF_TEMP + " ," + TABLE_NAME + " Gold ";
                //    strDet = strDet + " WHERE " + strWhere;
                //    strDet = strDet + "  AND NOT EXISTS (SELECT XREF_ID FROM " + TABLE_XREF_TEMPDETAIL;
                //    strDet = strDet + " where " + TABLE_XREF_TEMP + " .XREF_ID =" + TABLE_XREF_TEMPDETAIL + " .Xref_Id )  ";

                //    //strDet = strDet + " temp_xref.first_name =Gold.first_name";
                //    //strDet = strDet + " AND  temp_xref.last_name =Gold.last_name";
                //    // strDet = strDet + " AND temp_xref.title =Gold.title ";

                //    Util.WritInfoLog(ClsName, "matchRule", "method for insert into detail table -input_id,master_id and rule_id in #temp_id ", string.Format("Query:{0}", strDet));
                //    Int32 resUpd1 = Conn.SaveDataByFactory(strDet);
                //    Util.WritInfoLog(ClsName, "matchRule", "End method for insert into detail table -input_id,master_id and rule_id in #temp_id");

                //    //////////
                //    //strQry = " SELECT * FROM temp_id ";
                //    //strQry = strQry + " order by xref_id ";

                //    //Util.WritInfoLog(ClsName, "matchRule", "Query for getting dimension details", string.Format("Query:{0}", strQry));
                //    //DataSet ds1 = Conn.GetDataSetFromFactory(strQry);
                //    //Util.WritInfoLog(ClsName, "matchRule", "End Query for getting dimension details");
                //    ///////////////
                //    ///////                    
                //    //strUpdate = strUpdate + " temp_xref.first_name =Gold.first_name ";
                //    //strUpdate = strUpdate + "   AND  temp_xref.last_name =Gold.last_name ";
                //    //strUpdate = strUpdate + "  AND temp_xref.title =Gold.title ";
                //    //strUpdate = strUpdate + "  AND( temp_xref.MASTER_ID IS NULL AND temp_xref.MANUAL_RULE_ID IS NULL) ";                 
                //}

                //string strUpdate = "UPDATE C_IMPORT_DETAIL SET IS_EXECUTED ='TRUE' WHERE IMP_ID=" + Imp_Id;

                //Util.WritInfoLog(ClsName, "matchRule", "method for insert into detail table -input_id,master_id and rule_id in #temp_id ", string.Format("Query:{0}", strUpdate));
                //Int32 resUpd2 = Conn.SaveDataByFactory(strUpdate);
                //Util.WritInfoLog(ClsName, "matchRule", "End method for insert into detail table -input_id,master_id and rule_id in #temp_id");


                //colmodel and columns
                string strdata = GetMatchRuleColumns(true);

                DataTable dtDistinctRule = ds.Tables[0];

                List<DropdownListItem> ruleMaster = new List<DropdownListItem>();
                foreach (DataRow dr in dtDistinctRule.Rows)
                {
                    ruleMaster.Add(new DropdownListItem("Rule-" + dr["MATCH_RULE_ID"].ToString(), dr["MATCH_RULE_ID"].ToString()));
                }

                JavaScriptSerializer js = new JavaScriptSerializer();
                string strRule = js.Serialize(ruleMaster);

                return strdata + "^" + strRule;

            }
            catch (SqlException exs)
            {
                Util.WritErrorLog(ClsName, "matchRule", "SQL Exceptions Error in matchRule", "", exs);
                return "";
                throw exs;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "matchRule", "Error in matchRule", "", ex);
                return "";
                throw ex;
            }
        }

        protected string GenerateMatchRule(DataTable dt, string strtable)
        {
            try
            {
                string strmatchRule = string.Empty;

                foreach (DataRow row in dt.Rows) // Loop over the rules.
                {
                    if (strmatchRule == string.Empty)
                    {
                        strmatchRule = strtable + "." + row["FIELD_NAME"] + " = Gold." + row["FIELD_NAME"];
                    }
                    else
                    {
                        strmatchRule = strmatchRule + " AND " + strtable + "." + row["FIELD_NAME"] + " = Gold." + row["FIELD_NAME"];
                    }
                }

                return strmatchRule;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GenerateMatchRule", "Error in GenerateMatchRule", "", ex);
                return "";
                throw ex;
            }

        }

        public string GetImportdata()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));
                lstparam.Add(new DBParameter("@Imp_Id", DbType.Int32, ParameterDirection.Input, 0, Imp_Id));
                if (sidx == "" || sidx == null || sidx == "Row")
                {
                    sidx = " XREF_ID";
                }
                lstparam.Add(new DBParameter("@sidx", DbType.String, ParameterDirection.Input, 0, sidx));
                lstparam.Add(new DBParameter("@sord", DbType.String, ParameterDirection.Input, 0, sord));

                Util.WritInfoLog(ClsName, "GetImportdata", "procedure for getting import data", "");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetImportData", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetImportdata", "End procedure for getting import data");

                DataTable dt = ds.Tables[0];
                int cnt = 0;
                // cnt = Convert.ToInt32(ds.Tables[1].Rows[0]["CNT"]);
                cnt = dt.Rows.Count;
                string strData = Common.JsonForJqgrid(dt, rows, cnt, page);
                return strData;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetImportdata", "Error in GetImportdata", "", ex);
                throw ex;
            }
        }

        //original before SP
        //public string GetImportdata()
        //{
        //    try
        //    {

        //        Connection sqlConn = new Connection();

        //        string strQryPrim = " SELECT * FROM X_MASTER_LIST_COLUMNS ";
        //        strQryPrim = strQryPrim + " WHERE LIST_ID =" + ListId;
        //        strQryPrim = strQryPrim + " AND IS_PRIMARY ='TRUE'";

        //        strQryPrim = strQryPrim + " SELECT TABLE_XREF_TEMP FROM X_MASTER_LIST WHERE X_MASTER_LIST.LIST_ID=" + ListId;

        //        Util.WritInfoLog(ClsName, "GetImportdata", "Query for x_master_list_columns", string.Format("Query:{0}", strQryPrim));
        //        DataSet dsPrime = sqlConn.GetDataSetFromFactory(strQryPrim);
        //        Util.WritInfoLog(ClsName, "GetImportdata", "End Query for x_master_list_columns");

        //        string strPrimary = dsPrime.Tables[0].Rows[0]["FIELD_NAME"].ToString();

        //        if (sidx == "" || sidx == null || sidx == "Row")
        //        {
        //            //sidx = strPrimary;
        //            sidx = " XREF_ID";
        //        }

        //        //sidx = strPrimary;

        //        string sortExp = sidx + " " + sord;
        //        int start = ((page - 1) * rows);

        //        string strListCols = GetListColumns(false);
        //        strListCols = strListCols + " SOURCE,SOURCE_ID,MASTER_ID,MANUAL_RULE_ID,IMP_ID";

        //        string strQry = " SELECT ROW_NUMBER() ";
        //        strQry = strQry + " OVER (ORDER BY " + sortExp + " ) AS Row,* ";
        //        strQry = strQry + " FROM ( (";
        //        strQry = strQry + " SELECT XREF_ID,";
        //        //strQry = strQry + " DIM_ID,first_name,last_name,";
        //        //strQry = strQry + " SOURCE,SOURCE_ID,MASTER_ID,MANUAL_RULE_ID ";
        //        strQry = strQry + strListCols;
        //        strQry = strQry + " FROM " + dsPrime.Tables[1].Rows[0]["TABLE_XREF_TEMP"].ToString();
        //        strQry = strQry + " WHERE IMP_ID=" + Imp_Id;
        //        strQry = strQry + " ))tmp ;";

        //        strQry = strQry + " SELECT COUNT(*) CNT FROM( ";
        //        strQry = strQry + " SELECT ROW_NUMBER() ";
        //        strQry = strQry + " OVER (ORDER BY " + sortExp + ") AS Row,* ";
        //        strQry = strQry + " FROM ( (";
        //        strQry = strQry + " SELECT XREF_ID,";
        //        // strQry = strQry + " DIM_ID,first_name,last_name,";
        //        // strQry = strQry + " SOURCE,SOURCE_ID,MASTER_ID,MANUAL_RULE_ID ";
        //        strQry = strQry + strListCols;
        //        strQry = strQry + " FROM " + dsPrime.Tables[1].Rows[0]["TABLE_XREF_TEMP"].ToString();
        //        strQry = strQry + " WHERE IMP_ID=" + Imp_Id;
        //        strQry = strQry + " ))tmp ";
        //        strQry = strQry + " ) temp ;";

        //        Util.WritInfoLog(ClsName, "GetImportdata", "Query for getting import data", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "GetImportdata", "End Query for getting import datas");

        //        DataTable dt = ds.Tables[0];

        //        int cnt = 0;
        //        cnt = Convert.ToInt32(ds.Tables[1].Rows[0]["CNT"]);
        //        string strData = Common.JsonForJqgrid(dt, rows, cnt, page);
        //        return strData;

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "GetImportdata", "Error in GetImportdata", "", ex);
        //        throw ex;
        //    }
        //}

        public string saveMatchData()
        {
            try
            {

                Connection Conn = new Connection();

                var xmlDocument = new XmlDocument();
                var serializer = new XmlSerializer(typeof(List<LstIds>));
                using (var stream = new MemoryStream())
                {
                    serializer.Serialize(stream, LstIds);
                    stream.Flush(); stream.Seek(0, SeekOrigin.Begin);
                    xmlDocument.Load(stream);
                }

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@MatchIds_Xml", DbType.Xml, ParameterDirection.Input, 0, xmlDocument.OuterXml));
                lstparam.Add(new DBParameter("@RuleID", DbType.Int16, ParameterDirection.Input, 0, RuleId));
                lstparam.Add(new DBParameter("@tblName", DbType.String, ParameterDirection.Input, 255, dimTableName));
                lstparam.Add(new DBParameter("@List_ID", DbType.Int16, ParameterDirection.Input, 0, ListId));
                lstparam.Add(new DBParameter("@IMP_ID", DbType.Int16, ParameterDirection.Input, 0, Imp_Id));

                lstparam.Add(new DBParameter("@EndProc", DbType.Boolean, ParameterDirection.Output, 0, false));

                Util.WritInfoLog(ClsName, "saveMatchData", "method for savematchdata in _xref", string.Format("Query:{0}", ""));
                Int32 ret = Conn.SaveDataByFactoryByProc("usp_SaveMatchData", ref(lstparam));
                Util.WritInfoLog(ClsName, "saveMatchData", "method for savematchdata in _xref");

                bool bEndProc = false;
                bEndProc = lstparam.Any(paramm => paramm.name == "@EndProc" && Convert.ToBoolean(paramm.value) == true);

                if (bEndProc == true)
                { return "endproc"; }
                else
                { return "true"; }

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "saveMatchData", "Error in saveMatchData", "", ex);
                return "false";
                throw ex;
            }
        }

        public string saveManualMatchData()
        {
            try
            {

                Connection Conn = new Connection();

                var xmlDocument = new XmlDocument();
                var serializer = new XmlSerializer(typeof(List<LstManualIds>));

                using (var stream = new MemoryStream())
                {
                    serializer.Serialize(stream, LstManualIds);
                    stream.Flush(); stream.Seek(0, SeekOrigin.Begin);
                    xmlDocument.Load(stream);
                }

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@MatchIds_Xml", DbType.Xml, ParameterDirection.Input, 0, xmlDocument.OuterXml));
                lstparam.Add(new DBParameter("@RuleID", DbType.Int16, ParameterDirection.Input, 0, RuleId));
                lstparam.Add(new DBParameter("@tblName", DbType.String, ParameterDirection.Input, 255, dimTableName));
                lstparam.Add(new DBParameter("@List_ID", DbType.Int16, ParameterDirection.Input, 0, ListId));
                lstparam.Add(new DBParameter("@IMP_ID", DbType.Int16, ParameterDirection.Input, 0, Imp_Id));

                lstparam.Add(new DBParameter("@EndProc", DbType.Boolean, ParameterDirection.Output, 0, false));

                Util.WritInfoLog(ClsName, "saveMatchData", "method for save manual matchdata in _xref", string.Format("Query:{0}", ""));
                Int32 ret = Conn.SaveDataByFactoryByProc("usp_SaveManualMatchData", ref(lstparam));
                Util.WritInfoLog(ClsName, "saveMatchData", "method for save manual matchdata in _xref");

                bool bEndProc = false;
                bEndProc = lstparam.Any(paramm => paramm.name == "@EndProc" && Convert.ToBoolean(paramm.value) == true);

                if (bEndProc == true)
                { return "endproc"; }
                else
                { return "true"; }

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "saveManualMatchData", "Error in saveManualMatchData", "", ex);
                throw ex;
            }
        }

        public string saveUnMatchData()
        {
            try
            {


                Connection Conn = new Connection();

                var xmlDocument = new XmlDocument();
                var serializer = new XmlSerializer(typeof(List<LstIds>));

                using (var stream = new MemoryStream())
                {
                    serializer.Serialize(stream, LstIds);
                    stream.Flush(); stream.Seek(0, SeekOrigin.Begin);
                    xmlDocument.Load(stream);
                }

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@UnMatchIds_Xml", DbType.Xml, ParameterDirection.Input, 0, xmlDocument.OuterXml));
                lstparam.Add(new DBParameter("@RuleID", DbType.Int16, ParameterDirection.Input, 0, 0));
                lstparam.Add(new DBParameter("@tblName", DbType.String, ParameterDirection.Input, 255, dimTableName));
                lstparam.Add(new DBParameter("@List_ID", DbType.Int16, ParameterDirection.Input, 0, ListId));
                lstparam.Add(new DBParameter("@IMP_ID", DbType.Int16, ParameterDirection.Input, 0, Imp_Id));

                lstparam.Add(new DBParameter("@EndProc", DbType.Boolean, ParameterDirection.Output, 0, false));

                Util.WritInfoLog(ClsName, "saveUnMatchData", "method for saveUnMatchData in _xref and master", string.Format("Query:{0}", ""));
                Int32 ret = Conn.SaveDataByFactoryByProc("usp_SaveUnMatchData", ref(lstparam));
                Util.WritInfoLog(ClsName, "saveUnMatchData", "method for saveUnMatchData in _xref and master");

                bool bEndProc = false;
                bEndProc = lstparam.Any(paramm => paramm.name == "@EndProc" && Convert.ToBoolean(paramm.value) == true);

                if (bEndProc == true)
                { return "endproc"; }
                else
                { return "true"; }

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "saveUnMatchData", "Error in saveUnMatchData", "", ex);
                throw ex;
            }
        }

        public string MatchSendtoUnMatch()
        {
            try
            {

                Connection Conn = new Connection();

                //original code before SP

                //string strQry = " SELECT TABLE_XREF_TEMP,TABLE_XREF_TEMPDETAIL FROM X_MASTER_LIST WHERE X_MASTER_LIST.LIST_ID=" + ListId;

                //Util.WritInfoLog(ClsName, "MatchSendtoUnMatch", "Query for x_master_list_columns", string.Format("Query:{0}", strQry));
                //DataSet ds = Conn.GetDataSetFromFactory(strQry);
                //Util.WritInfoLog(ClsName, "MatchSendtoUnMatch", "End Query for x_master_list_columns");

                //string temp_xref = ds.Tables[0].Rows[0]["TABLE_XREF_TEMP"].ToString();
                //string temp_xref_detail = ds.Tables[0].Rows[0]["TABLE_XREF_TEMPDETAIL"].ToString();


                //////delete data from temp_xref of that rule_id
                ////strDel = " DELETE FROM temp_XREF ";
                ////strDel = strDel + " WHERE MANUAL_RULE_ID = " + RuleId.ToString();
                ////strDel = strDel + " AND XREF_ID IN (" + Ids + ")";

                ////Util.WritInfoLog(ClsName, "saveMatchData", "Query for delete data from temp_xref", string.Format("Query:{0}", strDel));
                ////ret = Conn.SaveDataByFactory(strDel);
                ////Util.WritInfoLog(ClsName, "saveMatchData", "End Query for delete data from temp_xref");

                //// take imp_id also 
                ////string strDel = " DELETE FROM " + temp_xref;
                ////strDel = strDel + " WHERE EXISTS ( ";
                ////strDel = strDel + " SELECT " + temp_xref_detail + ".XREF_ID FROM " + temp_xref_detail;
                ////strDel = strDel + " where Rule_Id = " + RuleId;
                ////strDel = strDel + " AND " + temp_xref_detail + ".Xref_Id =" + temp_xref + ".XREF_ID ";
                ////strDel = strDel + " GROUP BY Xref_Id ";
                ////strDel = strDel + " HAVING COUNT(" + temp_xref_detail + ".XREF_ID) =1 ) ";

                ////string strDel = " DELETE FROM " + temp_xref_detail;
                ////strDel = strDel + " WHERE EXISTS ( ";
                ////strDel = strDel + " SELECT " + temp_xref_detail + ".XREF_ID FROM " + temp_xref_detail;
                ////strDel = strDel + " where Rule_Id = " + RuleId;
                ////strDel = strDel + " AND " + temp_xref + ".IMP_ID =" + Imp_Id;
                ////strDel = strDel + " AND " + temp_xref_detail + ".Xref_Id =" + temp_xref + ".XREF_ID ";
                ////strDel = strDel + " GROUP BY Xref_Id ";
                ////strDel = strDel + " HAVING COUNT(" + temp_xref_detail + ".XREF_ID) =1 ) ";

                //string strDel = " DELETE " + temp_xref_detail + " FROM ( ";
                //strDel = strDel + " select XREF_ID from " + temp_xref_detail;
                //strDel = strDel + " WHERE RULE_ID = " + RuleId;
                //strDel = strDel + " AND IMP_ID = " + Imp_Id;
                //strDel = strDel + " group by XREF_ID having count(XREF_ID) = 1 ";
                //strDel = strDel + " ) tmp ";
                //strDel = strDel + " WHERE " + temp_xref_detail + ".XREF_ID = tmp.XREF_ID ";

                //Util.WritInfoLog(ClsName, "MatchSendtoUnMatch", "Query for Match send to unmatch", string.Format("Query:{0}", strDel));
                //Int32 ret = Conn.SaveDataByFactory(strDel);
                //Util.WritInfoLog(ClsName, "MatchSendtoUnMatch", "End Query for Match send to unmatch");


                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@List_ID", DbType.Int32, ParameterDirection.Input, 0, ListId));
                lstparam.Add(new DBParameter("@Imp_ID", DbType.Int32, ParameterDirection.Input, 0, Imp_Id));
                lstparam.Add(new DBParameter("@Rule_ID", DbType.Int32, ParameterDirection.Input, 0, RuleId));

                Util.WritInfoLog(ClsName, "discard", "method for usp_MatchSendtoUnMatch");
                int ret = Conn.SaveDataByFactoryByProc("usp_MatchSendtoUnMatch", ref(lstparam));
                Util.WritInfoLog(ClsName, "discard", "End method for usp_MatchSendtoUnMatch");

                return "true";

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "MatchSendtoUnMatch", "Error in MatchSendtoUnMatch", "", ex);
                throw ex;
            }
        }

        public string ManualSendtoUnMatch()
        {
            try
            {

                Connection Conn = new Connection();

                //original before SP

                //string strQry = " SELECT TABLE_XREF_TEMP,TABLE_XREF_TEMPDETAIL FROM X_MASTER_LIST WHERE X_MASTER_LIST.";
                //strQry = strQry + "LIST_ID=" + ListId;                

                //Util.WritInfoLog(ClsName, "ManualSendtoUnMatch", "Query for temp_xref tables", string.Format("Query:{0}", strQry));
                //DataSet ds = Conn.GetDataSetFromFactory(strQry);
                //Util.WritInfoLog(ClsName, "ManualSendtoUnMatch", "End Query for temp_xref tables");

                //string temp_xref = ds.Tables[0].Rows[0]["TABLE_XREF_TEMP"].ToString();
                //string temp_xref_detail = ds.Tables[0].Rows[0]["TABLE_XREF_TEMPDETAIL"].ToString();

                ////// take imp_id also 
                ////string strDel = " DELETE  FROM " + temp_xref;
                ////strDel = strDel + " WHERE EXISTS ( ";
                ////strDel = strDel + " SELECT " + temp_xref_detail + ".XREF_ID FROM " + temp_xref_detail;
                ////strDel = strDel + " where Rule_Id = " + RuleId;
                ////strDel = strDel + " AND " + temp_xref_detail + ".Xref_Id =" + temp_xref + ".XREF_ID ";
                ////strDel = strDel + " GROUP BY Xref_Id ";
                ////strDel = strDel + " HAVING COUNT(" + temp_xref_detail + ".XREF_ID) > 1 ) ";

                //string strDel = " DELETE " + temp_xref_detail + " FROM ( ";
                //strDel = strDel + " select XREF_ID from " + temp_xref_detail;
                //strDel = strDel + " WHERE RULE_ID = " + RuleId;
                //strDel = strDel + " AND IMP_ID = " + Imp_Id;
                //strDel = strDel + " group by XREF_ID having count(XREF_ID) > 1 ";
                //strDel = strDel + " ) tmp ";
                //strDel = strDel + " WHERE " + temp_xref_detail + ".XREF_ID = tmp.XREF_ID ";

                //Util.WritInfoLog(ClsName, "ManualSendtoUnMatch", "Query for Manual send to un-match", string.Format("Query:{0}", strDel));
                //Int32 ret = Conn.SaveDataByFactory(strDel);
                //Util.WritInfoLog(ClsName, "ManualSendtoUnMatch", "End Query for Manual send to un-match");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@List_ID", DbType.Int32, ParameterDirection.Input, 0, ListId));
                lstparam.Add(new DBParameter("@Imp_ID", DbType.Int32, ParameterDirection.Input, 0, Imp_Id));
                lstparam.Add(new DBParameter("@Rule_ID", DbType.Int32, ParameterDirection.Input, 0, RuleId));

                Util.WritInfoLog(ClsName, "discard", "method for ManualSendtoUnMatch");
                int ret = Conn.SaveDataByFactoryByProc("usp_ManualSendtoUnMatch", ref(lstparam));
                Util.WritInfoLog(ClsName, "discard", "End method for ManualSendtoUnMatch");

                return "true";
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "ManualSendtoUnMatch", "Error in ManualSendtoUnMatch", "", ex);
                return "false";
                throw ex;
            }
        }

        public string fillExistProcGrid()
        {
            try
            {

                string primarykey = string.Empty;
                Connection sqlConn = new Connection();

                Util.WritInfoLog(ClsName, "fillExistProcGrid", "procedure for fillExistProcGrid", "usp_GetImportDetails");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetImportDetails");
                Util.WritInfoLog(ClsName, "fillExistProcGrid", "End procedure for fillExistProcGrid");

                Grid griddata = new Grid();
                Common common = new Common();
                DataTable dt = ds.Tables[1];

                foreach (DataRowView dr in dt.DefaultView)
                {
                    if (dr["FIELD_NAME"].ToString() == "DIM_ID" || dr["FIELD_NAME"].ToString() == "TABLE_NAME" || dr["FIELD_NAME"].ToString() == "IS_EXECUTED")
                    {
                        dr["IS_HIDDEN"] = 1;
                    }
                }

                Util.WritInfoLog(ClsName, "fillExistProcGrid", "SetColModel for exist process grid", "");
                griddata = common.SetColModel(griddata, ds.Tables[1]);
                Util.WritInfoLog(ClsName, "fillExistProcGrid", "End SetColModel for exist process grid");

                List<object> rows = new List<object>();

                foreach (DataRow dr in ds.Tables[0].Rows)
                {

                    Dictionary<string, object> row = new Dictionary<string, object>();
                    // row.Add("select", "select");

                    foreach (DataColumn dc in ds.Tables[0].Columns)
                    {
                        row.Add(dc.ColumnName, dr[dc].ToString());
                    }
                    rows.Add(row);
                }

                griddata.datarow = rows;

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(griddata);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "fillExistProcGrid", "Error in fillExistProcGrid", "", ex);
                throw ex;
            }
        }

        public string fillExistProcGridData()
        {
            try
            {

                string primarykey = string.Empty;
                Connection Conn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                if (ListId != 0)
                {
                    lstparam.Add(new DBParameter("@DIM_ID", DbType.Int16, ParameterDirection.Input, 0, ListId));
                }

                Util.WritInfoLog(ClsName, "fillExistProcGridData", "procedure for fillExistProcGridData", "usp_GetImportDetailsByDimId");
                DataSet ds = Conn.GetDataSetFromFactoryProc("usp_GetImportDetailsByDimId", ref(lstparam));
                Util.WritInfoLog(ClsName, "fillExistProcGridData", "End procedure for fillExistProcGridData");

                DataTable dt = ds.Tables[0];

                int cnt = 0;
                cnt = Convert.ToInt32(ds.Tables[0].Rows.Count);
                string strData = Common.JsonForJqgrid(dt, rows, cnt, page);

                return strData;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "fillExistProcGridData", "Error in fillExistProcGridData", "", ex);
                return "false";
                throw ex;
            }
        }

        public string discard()
        {
            try
            {

                Connection Conn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@List_ID", DbType.Int16, ParameterDirection.Input, 0, ListId));
                lstparam.Add(new DBParameter("@IMP_ID", DbType.Int16, ParameterDirection.Input, 0, Imp_Id));
                lstparam.Add(new DBParameter("@EndProc", DbType.Boolean, ParameterDirection.Output, 0, false));

                Util.WritInfoLog(ClsName, "discard", "discard method for unmatched");
                int ret = Conn.SaveDataByFactoryByProc("usp_DiscardUnmatched", ref(lstparam));
                Util.WritInfoLog(ClsName, "discard", "End of discard method for unmatched");

                bool bEndProc = false;
                bEndProc = lstparam.Any(paramm => paramm.name == "@EndProc" && Convert.ToBoolean(paramm.value) == true);

                if (bEndProc == true)
                { return "endproc"; }
                else
                { return "true"; }

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "discard", "Error in discard", "", ex);
                return "false";
                throw ex;
            }
        }

        public string ShowExistProc()
        {
            try
            {

                Util.WritInfoLog(ClsName, "ShowExistProc", "Start Creating Connection from Common");
                Connection Conn = new Connection();
                Util.WritInfoLog(ClsName, "ShowExistProc", "End Creating Connection from Common");

                //original before SP
                ////field name of dimension
                //string strQry = " SELECT * FROM vw_MatchRule ";
                //strQry = strQry + " WHERE LIST_ID =" + ListId;

                //Util.WritInfoLog(ClsName, "ShowExistProc", "Query for getting dimension details", string.Format("Query:{0}", strQry));
                //DataSet ds = Conn.GetDataSetFromFactory(strQry);
                //Util.WritInfoLog(ClsName, "ShowExistProc", "End Query for getting dimension details");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));

                Util.WritInfoLog(ClsName, "ShowExistProc", "method for ShowExistProc");
                DataSet ds = Conn.GetDataSetFromFactoryProc("usp_GetExistingImpProc", ref(lstparam));
                Util.WritInfoLog(ClsName, "ShowExistProc", "End method for ShowExistProc");


                DataTable dt = ds.Tables[0];
                DataTable dtDistinctRule = dt.DefaultView.ToTable(true, "MATCH_RULE_ID");

                //colmodel and columns
                string strdata = GetMatchRuleColumns(true);

                List<DropdownListItem> ruleMaster = new List<DropdownListItem>();
                foreach (DataRow dr in dtDistinctRule.Rows)
                {
                    ruleMaster.Add(new DropdownListItem("Rule-" + dr["MATCH_RULE_ID"].ToString(), dr["MATCH_RULE_ID"].ToString()));
                }

                JavaScriptSerializer js = new JavaScriptSerializer();
                string strRule = js.Serialize(ruleMaster);

                return strdata + "^" + strRule;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "ShowExistProc", "Error in ShowExistProc", "", ex);
                throw ex;
            }
        }

        //import Data Relation
        public string ImportDataRel()
        {
            try
            {

                Util.WritInfoLog(ClsName, "ImportDataRel", "Start Creating Connection from Common");
                Connection Conn = new Connection();
                Util.WritInfoLog(ClsName, "ImportDataRel", "End Creating Connection from Common");

                //check if sheetname exists                
                Util.WritInfoLog(ClsName, "ImportDataRel", "method to be executed for Excel sheet name", string.Format("Query:{0}", "GetSheetNameFromExcel"));
                DataTable dtSheetExcel = Conn.GetSheetNameFromExcel(strfilePath);
                Util.WritInfoLog(ClsName, "ImportDataRel", "method to be executed for Excel sheet name");

                strSheetName = strSheetName + "$";
                Boolean bSheetExist = false;

                foreach (DataRow dr in dtSheetExcel.Rows)
                {
                    if (dr[2].ToString().Trim().ToLower() == strSheetName.Trim().ToLower())
                    {
                        bSheetExist = true;
                        break;
                    }
                }

                if (bSheetExist == true)
                {

                    string strQryEx = " SELECT * FROM [" + strSheetName + "] ";

                    Util.WritInfoLog(ClsName, "ImportDataRel", "Query to be executed for Excel", string.Format("Query:{0}", strQryEx));
                    DataSet dsExcel = Conn.GetDataSetFromExcel(strQryEx, strfilePath);
                    Util.WritInfoLog(ClsName, "ImportDataRel", "End Query to be executed for Excel");
                    DataTable tempXref = dsExcel.Tables[0];


                    string[] excelCols = (from DataColumn x in tempXref.Columns
                                          select x.ColumnName).ToArray();

                    int cnt = 0;

                    if (excelCols.Length == 2)
                    {

                        List<DBParameter> lstparam1 = new List<DBParameter>();
                        lstparam1.Add(new DBParameter("@MList_Id", DbType.Int32, ParameterDirection.Input, 0, ListId));
                        lstparam1.Add(new DBParameter("@CList_Id", DbType.Int32, ParameterDirection.Input, 0, ChildListId));
                        lstparam1.Add(new DBParameter("@MastColumnName", DbType.String, ParameterDirection.Input, 0, excelCols[0]));
                        lstparam1.Add(new DBParameter("@ChildColumnName", DbType.String, ParameterDirection.Input, 0, excelCols[1]));
                        // lstparam1.Add(new DBParameter("@MastColumnName", DbType.String, ParameterDirection.Input, 0, "SYSTEM_PRIMARY_KEY"));
                        // lstparam1.Add(new DBParameter("@ChildColumnName", DbType.String, ParameterDirection.Input, 0, "SYSTEM_PRIMARY_KEY"));

                        Util.WritInfoLog(ClsName, "ImportDataRel", "method for validating columns of dimension");
                        cnt = Convert.ToInt16(Conn.ExecuteScalarByFactory("usp_ValidateImportRel", ref(lstparam1)));
                        Util.WritInfoLog(ClsName, "ImportDataRel", "End method for validating columns of dimension");
                    }

                    ConfigData config = new ConfigData();
                    Boolean bmap = true;
                    if (cnt < 2)
                    {
                        bmap = false;
                    }

                    if (bmap == true)
                    {

                        HMSPrincipal principal;
                        principal = (HMSPrincipal)Thread.CurrentPrincipal;
                        HMSIdentity userIdentity = principal.identity;

                        Int32 userid = userIdentity.userPK;

                        //save in import details
                        List<DBParameter> lstparam = new List<DBParameter>();
                        lstparam.Add(new DBParameter("@MList_Id", DbType.Int32, ParameterDirection.Input, 0, ListId));
                        lstparam.Add(new DBParameter("@CList_Id", DbType.Int32, ParameterDirection.Input, 0, ChildListId));
                        lstparam.Add(new DBParameter("@CREATED_BY", DbType.Int32, ParameterDirection.Input, 0, userid));//user_id
                        lstparam.Add(new DBParameter("@MastColumnName", DbType.String, ParameterDirection.Input, 0, excelCols[0]));
                        lstparam.Add(new DBParameter("@ChildColumnName", DbType.String, ParameterDirection.Input, 0, excelCols[1]));
                        lstparam.Add(new DBParameter("@REL_ID", DbType.Int16, ParameterDirection.Output, 0, 0));//rel_id
                        lstparam.Add(new DBParameter("@RelTable", DbType.String, ParameterDirection.Output, 255, 0));//rel_id

                        Util.WritInfoLog(ClsName, "ImportDataRel", "procedure for saving import details", "usp_SaveImportDetails");
                        int ret = Conn.SaveDataByFactoryByProc("usp_SaveImportRelDetails", ref(lstparam));
                        Util.WritInfoLog(ClsName, "ImportDataRel", "End procedure for saving import details");

                        int REL_ID = 0;
                        string strRelTable = string.Empty;

                        foreach (DBParameter dbparam in lstparam)
                        {
                            if (dbparam.name == "@REL_ID")
                            {
                                REL_ID = Convert.ToInt32(dbparam.value);
                                //break;
                            }
                            if (dbparam.name == "@RelTable")
                            {
                                strRelTable = Convert.ToString(dbparam.value);
                                break;
                            }
                        }

                        // tempXref.Columns[0].ColumnName = "MASTER_ID";
                        // tempXref.Columns[1].ColumnName = "CHILD_ID";

                        //DataColumn dcol = new DataColumn("REL_ID", typeof(System.Int32));
                        //tempXref.Columns.Add(dcol);

                        ////set rel_id in temp_xref
                        //foreach (DataRow dr in tempXref.Rows)
                        //{
                        //    dr["REL_ID"] = REL_ID;
                        //}

                        string strSelect = " SELECT * FROM " + strRelTable + " WHERE 1=0 ";

                        Util.WritInfoLog(ClsName, "ImportDataRel", "method for bulk insert from excel to relational db", string.Format("Query:{0}", strSelect));
                        Boolean bflag = Conn.BatchUpdatedbWithAdapter(strSelect, tempXref, 50);
                        Util.WritInfoLog(ClsName, "ImportDataRel", "End method for bulk insert from excel to relational db");

                        //update dim_id in realtion table
                        List<DBParameter> lstparam2 = new List<DBParameter>();
                        lstparam2.Add(new DBParameter("@MList_Id", DbType.Int32, ParameterDirection.Input, 0, ListId));
                        lstparam2.Add(new DBParameter("@CList_Id", DbType.Int32, ParameterDirection.Input, 0, ChildListId));

                        Util.WritInfoLog(ClsName, "ImportDataRel", "procedure for update dim_id in realtion table", "usp_UpdateRelationTable");
                        ret = Conn.SaveDataByFactoryByProc("usp_UpdateRelationTable", ref(lstparam2));
                        Util.WritInfoLog(ClsName, "ImportDataRel", "End update dim_id in realtion table");

                        //return strdata + "~" + IMP_ID;
                        return "" + "~" + REL_ID;

                    }
                    else
                    {
                        return "notmapped";
                    }
                }
                else
                {
                    return "noSheet";
                }
            }
            catch (SqlException exs)
            {
                Util.WritErrorLog(ClsName, "ImportDataRel", "SQL Exceptions Error in ImportDataRel", "", exs);
                return "false";
                throw exs;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "ImportDataRel", "Error in ImportDataRel", "", ex);
                return "false";
                throw ex;
            }
        }

        //to get selected dimension relation data
        public string GetMasterRelData()
        {
            try
            {

                // Util.WritInfoLog(ClsName, "GetMasterRelData", "Start Creating Connection from Common");
                Connection sqlConn = new Connection();
                // Util.WritInfoLog(ClsName, "GetMasterRelData", "End Creating Connection from Common");               

                //List<DBParameter> lstparam = new List<DBParameter>();
                //lstparam.Add(new DBParameter("@MList_Id", DbType.Int32, ParameterDirection.Input, 0, ListId));
                //lstparam.Add(new DBParameter("@CList_Id", DbType.Int32, ParameterDirection.Input, 0, ChildListId));
                //lstparam.Add(new DBParameter("@MasterId", DbType.Int32, ParameterDirection.Input, 0, MasterId));

                //Util.WritInfoLog(ClsName, "GetMasterRelData", "method for GetMasterData");
                //DataSet dsRel = sqlConn.GetDataSetFromFactoryProc("usp_GetMasterSourceData", ref(lstparam));
                //Util.WritInfoLog(ClsName, "GetMasterRelData", "End method for GetMasterData");

                //DataTable dt = dsRel.Tables[0];

                //JavaScriptSerializer js = new JavaScriptSerializer();
                //return "";

                ////////////////////
                string primarykey = string.Empty;
                string strData = string.Empty;

                //if (sidx == "" || sidx == null)
                //{
                sidx = "Child.DIM_ID ";
                //}

                string sortExp = sidx + " " + sord;
                int start = ((page - 1) * rows);

                List<DBParameter> lstparam = new List<DBParameter>();

                lstparam.Add(new DBParameter("@MList_Id", DbType.Int32, ParameterDirection.Input, 0, ListId));
                lstparam.Add(new DBParameter("@CList_Id", DbType.Int32, ParameterDirection.Input, 0, ChildListId));
                lstparam.Add(new DBParameter("@MasterId", DbType.Int32, ParameterDirection.Input, 0, MasterId));

                lstparam.Add(new DBParameter("@start", DbType.Int32, ParameterDirection.Input, 0, start));
                lstparam.Add(new DBParameter("@rows", DbType.Int32, ParameterDirection.Input, 0, rows));
                lstparam.Add(new DBParameter("@query", DbType.String, ParameterDirection.Input, 1000, query.Replace("\"", "'")));
                lstparam.Add(new DBParameter("@sortExp", DbType.String, ParameterDirection.Input, 1000, sortExp));
                lstparam.Add(new DBParameter("@_search", DbType.Boolean, ParameterDirection.Input, 0, _search));
                //lstparam.Add(new DBParameter("@dimTableName", DbType.String, ParameterDirection.Input, 1000, dimTableName));

                Util.WritInfoLog(ClsName, "GetMasterRelData", "method for GetMasterRelData");
                DataSet dsdata = sqlConn.GetDataSetFromFactoryProc("usp_GetRelChildData", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetMasterRelData", "End method for GetMasterRelData");

                //do for paging 
                if (dsdata.Tables.Count != 0)
                {
                    DataTable dtData = dsdata.Tables[0];
                    //dtData.Columns.Remove("Row");

                    int cnt = 0;
                    cnt = Convert.ToInt32(dsdata.Tables[1].Rows[0]["CNT"]);
                    strData = Common.JsonForJqgrid(dtData, rows, cnt, page);
                }

                JavaScriptSerializer js = new JavaScriptSerializer();
                return strData;


            }
            catch (SqlException exs)
            {
                Util.WritErrorLog(ClsName, "GetMasterRelData", "SQL Exceptions Error in GetMasterRelData", "", exs);
                return "";
                throw exs;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetMasterRelData", "Error in GetMasterRelData", "", ex);
                return "";
                throw ex;
            }
        }

        //to get selected dimension relation data
        public string GetMasterRelDataGraph()
        {
            try
            {

                Connection sqlConn = new Connection();

                var xmlDocument = new XmlDocument();
                var serializer = new XmlSerializer(typeof(String[]));

                using (var stream = new MemoryStream())
                {
                    serializer.Serialize(stream, arrMastId);
                    stream.Flush(); stream.Seek(0, SeekOrigin.Begin);
                    xmlDocument.Load(stream);
                }



                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@MList_Id", DbType.Int32, ParameterDirection.Input, 0, ListId));
                lstparam.Add(new DBParameter("@ID_XML", DbType.Xml, ParameterDirection.Input, 0, xmlDocument.OuterXml));

                Util.WritInfoLog(ClsName, "GetMasterRelDataGraph", "method for GetMasterRelDataGraph");
                DataSet dsdata = sqlConn.GetDataSetFromFactoryProc("usp_GetRelDataGraph", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetMasterRelDataGraph", "End method for GetMasterRelDataGraph");

                //DataView dvData = new DataView(dsdata.Tables[0]);
                //dvData.RowFilter = " ";
                Int32 ChildListId = Convert.ToInt32(dsdata.Tables[0].Rows[0][0]);
                string data = string.Empty;

                if (ChildListId != 0)
                {
                    List<RelObject> lstRels = new List<RelObject>();
                    foreach (DataRow dr in dsdata.Tables[1].Rows)
                    {
                        RelObject rel = new RelObject(dr["COL1"].ToString(), dr["COL2"].ToString());
                        lstRels.Add(rel);
                    }

                    JavaScriptSerializer js = new JavaScriptSerializer();
                    data = js.Serialize(lstRels);
                }
                //   return data;
                return ChildListId.ToString() + "^" + data;
                // return data + "^" + data;   
            }
            catch (SqlException exs)
            {
                Util.WritErrorLog(ClsName, "GetMasterRelDataGraph", "SQL Exceptions Error in GetMasterRelDataGraph", "", exs);
                return "";
                throw exs;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetMasterRelDataGraph", "Error in GetMasterRelDataGraph", "", ex);
                return "";
                throw ex;
            }
        }

        //to get selected dimension relation data
        public string GetRelDataChildGraph()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@MList_Id", DbType.Int32, ParameterDirection.Input, 0, ListId));
                lstparam.Add(new DBParameter("@Parent_Id", DbType.Int64, ParameterDirection.Input, 0, ParentId));

                Util.WritInfoLog(ClsName, "GetRelDataChildGraph", "method for GetRelDataChildGraph");
                DataSet dsdata = sqlConn.GetDataSetFromFactoryProc("usp_GetRelDataChildGraph", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetRelDataChildGraph", "End method for GetRelDataChildGraph");

                //DataView dvData = new DataView(dsdata.Tables[0]);
                //dvData.RowFilter = " ";

                List<RelChildObject> lstChildRels = new List<RelChildObject>();
                foreach (DataRow dr in dsdata.Tables[0].Rows)
                {
                    RelChildObject rel = new RelChildObject(Convert.ToInt64(dr["ParentId"]), Convert.ToInt64(dr["Id"]), dr["Value"].ToString());
                    lstChildRels.Add(rel);
                }

                Int32 ChildListId = Convert.ToInt32(dsdata.Tables[1].Rows[0][0]);


                JavaScriptSerializer js = new JavaScriptSerializer();
                // return js.Serialize(lstChildRels);
                //  return ChildListId + "^" + js.Serialize(lstChildRels);
                string data = js.Serialize(lstChildRels);
                return Convert.ToString(ChildListId) + "^" + data;


            }
            catch (SqlException exs)
            {
                Util.WritErrorLog(ClsName, "GetMasterRelDataGraph", "SQL Exceptions Error in GetMasterRelDataGraph", "", exs);
                return "";
                throw exs;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetMasterRelDataGraph", "Error in GetMasterRelDataGraph", "", ex);
                return "";
                throw ex;
            }
        }


        //to get selected relation data detail
        public string GetRelDataDetail()
        {
            try
            {

                Connection sqlConn = new Connection();
                if (ParentIds == "")
                {
                    ParentId = 0;
                }
                else { ParentId = -1; }

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@List_Id", DbType.Int32, ParameterDirection.Input, 0, ListId));
                lstparam.Add(new DBParameter("@RefId", DbType.Int64, ParameterDirection.Input, 0, MasterId));
                lstparam.Add(new DBParameter("@parent_Id", DbType.Int64, ParameterDirection.Input, 0, ParentId));

                Util.WritInfoLog(ClsName, "GetRelDataDetail", "method for GetRelDataDetail");
                DataSet dsDim = sqlConn.GetDataSetFromFactoryProc("usp_GetMasterSourceDataRel", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetRelDataDetail", "End method for GetRelDataDetail");

                DataTable dt = dsDim.Tables[0];

                DataTable DtDim = dsDim.Tables[1];
                DataTable DtDim_XREF = dsDim.Tables[2];
                //DataTable transposedTable_xref = GenerateTransposedTable(dsDim.Tables[1]);
                // DataTable transposedTable_xref = GenerateTransposedTableXref(dsDim.Tables[1], dsDim.Tables[3]);
                DataTable transposedTable_gray = GenerateTransposedTable(dsDim.Tables[3]);
                //DataTable transposedTable_hist = GenerateTransposedTable(dsDim.Tables[4]);

                //heading list 
                //List<string> dimheading = new List<string>();
                List<dimheading> dimheading = new List<dimheading>();

                dimheading dimhead = new dimheading();
                dimhead.text = "Fields";
                dimhead.colspan = "1";
                dimheading.Add(dimhead);

                dimheading dimheadmast = new dimheading();
                dimheadmast.text = "Master Value";
                dimheadmast.id = "thm" + MasterId.ToString();
                dimheadmast.val = MasterId.ToString();
                dimheadmast.colspan = "2";
                dimheading.Add(dimheadmast);

                int index = 0;

                foreach (DataRow dr in dsDim.Tables[2].Rows)
                {
                    dimheading dimheadx = new dimheading();
                    dimheadx.text = dr["Source"].ToString();
                    dimheadx.id = "thxref_" + index.ToString();//Convert.ToInt32(dr["XREF_ID"]) ;
                    dimheadx.val = Convert.ToString(dr["XREF_ID"]);
                    dimheadx.colspan = "1";
                    dimheading.Add(dimheadx);
                    index++;
                }

                foreach (DataRow dr in dsDim.Tables[3].Rows)
                {
                    dimheading dimheadg = new dimheading();
                    dimheadg.text = dr["Source"].ToString();
                    dimheadg.id = "grey" + "0";// Convert.ToString(dr["GREY_ID"]);
                    dimheadg.val = Convert.ToString(dr["GREY_ID"]);
                    dimheading.Add(dimheadg);
                    //dimheading.Add(dr["Source"].ToString());
                }

                //upper heading
                List<th> dimUpperHeading = new List<th>();

                th th = new th();
                th.colspan = 3;
                th.text = "Master";

                th th_xref = new th();
                th_xref.colspan = dsDim.Tables[2].Rows.Count;
                th_xref.text = "Source";

                th th_possible = new th();
                th_possible.colspan = dsDim.Tables[3].Rows.Count;
                th_possible.text = "Possible";

                dimUpperHeading.Add(th);
                dimUpperHeading.Add(th_xref);
                dimUpperHeading.Add(th_possible);

                //DataView dv_xref = new DataView(dsDim.Tables[1]); 
                //dv_xref.RowFilter=

                //string[]  arrhead = (from DataRow dr in dt.Rows where (string)dr.== "Source"); 

                //DataView Dv_xref = new DataView(transposedTable);
                //List<DataRow> xrefC_val = new List<DataRow>();
                //List<object> xrefC_vall = new List<object>();

                // transposedTable.Rows[0].
                //xrefC_vall =(List<object>) DtDim_XREF.Rows[0];

                int cnt = 0;
                List<DimensionMFields> listDimFlds = new List<DimensionMFields>();

                //
                DataView dvCol = new DataView(dt);
                dvCol.RowFilter = "IS_PRIMARY=FALSE";

                //foreach (DataRow dr in ds.Tables[0].Rows)
                foreach (DataRowView dr in dvCol)
                {


                    DimensionMFields dimField = new DimensionMFields();
                    dimField.name = dr["DISPLAY"].ToString();
                    dimField.size = 10;

                    if (DtDim.Rows.Count > 0)
                    {
                        dimField.value = DtDim.Rows[0][dr["FIELD_NAME"].ToString().Trim()].ToString();
                        if (DtDim.Rows[0][dr["FIELD_NAME"].ToString().Trim()].ToString().Length > 10)
                        {
                            dimField.size = DtDim.Rows[0][dr["FIELD_NAME"].ToString().Trim()].ToString().Length + 6;
                        }
                    }

                    int xref_id = 0;
                    if (dsDim.Tables[3].Rows.Count > 0)
                    {
                        // xref_id = Convert.ToInt32(dsDim.Tables[3].Rows[0][dr["FIELD_NAME"].ToString()]);
                        xref_id = Convert.ToInt32(dsDim.Tables[2].Rows[0]["XREF_ID"]);
                    }

                    dimField.xref_id = xref_id;

                    //object[] xrefArr_value = transposedTable_xref.Rows[cnt].ItemArray;
                    object[] grayArr_value = transposedTable_gray.Rows[cnt].ItemArray;

                    //getlist of xref objects column-wise
                    List<xref> lstxref = new List<xref>();
                    lstxref = GenerateXrefArray(dsDim.Tables[2], dsDim.Tables[4], dr["FIELD_NAME"].ToString().Trim());

                    dimField.xrefArr_value = lstxref;
                    dimField.grayArr_value = grayArr_value;

                    dimField.is_req = dr["IS_REQUIRED"].ToString();
                    dimField.datatype = dr["DATA_TYPE"].ToString().ToLower();
                    dimField.prec = dr["PRECISION"].ToString();
                    dimField.control_type = dr["CONTROL_TYPE"].ToString();
                    dimField.control_name = dr["FIELD_NAME"].ToString();

                    //for control type dropdown
                    if (dr["DROPDOWN_VAL"].ToString() != "")
                    {

                        List<DropdownListItem> listFieldDrpitems = new List<DropdownListItem>();

                        string[] arrDrpval = dr["DROPDOWN_VAL"].ToString().Split(',');
                        //add default select 
                        DropdownListItem DropdownListItemd = new DropdownListItem();
                        DropdownListItemd.Text = "--select--";
                        DropdownListItemd.Value = "";
                        listFieldDrpitems.Add(DropdownListItemd);

                        for (int i = 0; i < arrDrpval.Length; i++)
                        {

                            DropdownListItem DropdownListItem = new DropdownListItem();
                            DropdownListItem.Text = arrDrpval[i];
                            DropdownListItem.Value = arrDrpval[i];

                            if (arrDrpval[i] == DropdownListItem.Value)
                            {
                                DropdownListItem.selected = "true";
                            }
                            else
                            {
                                DropdownListItem.selected = "false";
                            }

                            listFieldDrpitems.Add(DropdownListItem);
                        }

                        dimField.fielddrp_item = listFieldDrpitems;
                    }

                    listDimFlds.Add(dimField);
                    cnt++;
                }

                DimensionMaster Dimmast = new DimensionMaster();
                Dimmast.DimMFields = listDimFlds;
                Dimmast.dimheading = dimheading;
                Dimmast.dimUpperHeading = dimUpperHeading;

                //foreach (DataRow row in DtDim_XREF.Rows)
                //{
                //   // xref_val.Add(row[dr["FIELD_NAME"].ToString()]);
                //    //object obj=row[dr["FIELD_NAME"].ToString()];

                //}               

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(Dimmast);
                //return js.Serialize(listDimFlds);

            }
            catch (SqlException exs)
            {
                Util.WritErrorLog(ClsName, "GetMasterRelDataGraph", "SQL Exceptions Error in GetMasterRelDataGraph", "", exs);
                return "";
                throw exs;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetMasterRelDataGraph", "Error in GetMasterRelDataGraph", "", ex);
                return "";
                throw ex;
            }
        }


        //without SOURCE
        ////to get selected relation data detail

        //public string GetRelDataDetail()
        //{
        //    try
        //    {

        //        Connection sqlConn = new Connection();
        //        if (ParentIds == "")
        //        {
        //            ParentId = 0;
        //        }
        //        else { ParentId = -1; }

        //        List<DBParameter> lstparam = new List<DBParameter>();
        //        lstparam.Add(new DBParameter("@List_Id", DbType.Int32, ParameterDirection.Input, 0, ListId));
        //        lstparam.Add(new DBParameter("@master_Id", DbType.Int64, ParameterDirection.Input, 0, MasterId));
        //        lstparam.Add(new DBParameter("@parent_Id", DbType.Int64, ParameterDirection.Input, 0, ParentId));

        //        Util.WritInfoLog(ClsName, "GetRelDataDetail", "method for GetRelDataDetail");
        //        DataSet dsdata = sqlConn.GetDataSetFromFactoryProc("usp_GetRelDataDetail", ref(lstparam));
        //        Util.WritInfoLog(ClsName, "GetRelDataDetail", "End method for GetRelDataDetail");

        //        //DataView dvData = new DataView(dsdata.Tables[0]);
        //        //dvData.RowFilter = " ";

        //        DataTable dtVal = dsdata.Tables[1];

        //        List<RelChildObject> lstChildRels = new List<RelChildObject>();
        //        List<Columns> listCols = new List<Columns>();

        //        foreach (DataRow dr in dsdata.Tables[0].Rows)
        //        {

        //            Columns col = new Columns(dr["DISPLAY"].ToString(), dtVal.Rows[0][dr["FIELD_NAME"].ToString().Trim()].ToString());
        //            listCols.Add(col);
        //        }

        //        Int32 ChildListId = Convert.ToInt32(dsdata.Tables[1].Rows[0][0]);

        //        JavaScriptSerializer js = new JavaScriptSerializer();
        //        return js.Serialize(listCols);
        //        ////  return ChildListId + "^" + js.Serialize(lstChildRels);
        //        //string data = js.Serialize(listCols);
        //        //return Convert.ToString(ChildListId) + "^" + data;

        //    }
        //    catch (SqlException exs)
        //    {
        //        Util.WritErrorLog(ClsName, "GetMasterRelDataGraph", "SQL Exceptions Error in GetMasterRelDataGraph", "", exs);
        //        return "";
        //        throw exs;
        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "GetMasterRelDataGraph", "Error in GetMasterRelDataGraph", "", ex);
        //        return "";
        //        throw ex;
        //    }
        //}


    }

}

